Table and Relationship design help (1 Viewer)

Cwittmaack · Jan 25, 2013

Hi All,
I’m creating a database in Access 2010 and would like someone look at it and tell me if I’m creating the tables correctly and if my relationships are correct. Those that look at it thank you So much. I’m open for suggestions good or bad.
Chuck.

plog · Jan 25, 2013

Overall, without data, its difficult to get down to specific errors. However, I'm fairly certain your relationships among your tables are not correct. Its like a spider web in there. You shouldn't be able to travel in circles in your relationships like that. I think it mostly stems from a lot of your tables having more than 1 foreign key. That in itself isn't bad, but its always the same keys (EMPID, POSITID, DEPTID). This relationship among those 3 keys should probably be only be established in one table, then the ID of that table should be used elsewhere.

As for the tables themselves, I see some numerated field names (DRUG1, DRUG2, etc.) which is a sign of a poor table structure. Data like that should either be broken out into another 1-many table.

If you could provide some data in those tables and then a plain-English explanantion of what this database is for I could give more specific advise.

Cwittmaack · Jan 25, 2013

Ok will post a more detailed breakdown. and will enter some data in each table. then repost table. thanks plog

Cwittmaack · Jan 28, 2013

Plog
The database is to keep records of employee information to include Emergency Contact information, Tardiness, Absents, Drug testing, Counseling Verbal or written.
The REF table will hold static short descriptions; this will allow a compact report to be produced of the verbal warning of a full counseling report but not the entire counseling.
Drug table is to hold the results of the random drug testing results which can be placed in a report
All information is tied to the Employee ID
Should I merge the EMP (employee) table with the EMG (emergency contact table) to 1 table
Place Department and position into the Company Table

Cwittmaack · Jan 28, 2013

Plog
After looking at the tables im going to dump the tardy and absent table into one (dup info) will repost later, thanks for the eye opening.

plog · Jan 28, 2013

REF table looks fine.

Drug table doesn't seem normalized for reasons I stated before (numerated field names). Also, I still don't think you need 3 foreign keys in this table--one will do. My guess is the COMPID in Company is the one to use. I would combine the fields TESTDATE and TESTTIME fields into one Date/Time field. Are the TESTPOS and TESTNEG complementary fields? Can they both be 'Yes' in the same record? Can they both be 'No'? My guess is that if one is yes the other is no--you don't need seperate fields then. Like your numerated DRUG fields, I would break off the fields AMP through THC into a new table.

EMP and EMG should be merged if they have a 1-1 relationship, which I think they do. Department and Position should be in the Company Table.

Also, what do you hope the LASTUPD fields are going to do? Its going to let you know that data in that row was changed, but its not going to tell you what data. Seems like a worthless field.

Cwittmaack · Jan 28, 2013

The Department and position tables were to be preloaded with the information and then selected from a dropdown., The LASTUP field was to be used for Upper-management incase there was a dispute over a record's content, they would be able to to see if the record was changed after the origenal create date of the record.

Cwittmaack · Jan 28, 2013

sorry plog

On the drug table, yes on the positive and negative results, the AMP - THC is a listing of drugs that can be tested for, and if one is positive they would select that drug, I agree on the test date and time the employee must list all the drugs that they are currently takeing, hence drug1 - drug? fields when being tested.

Cwittmaack · Jan 28, 2013

Plog
I have attached a Text file for you so that you can get a better idea of what im trying to do. I have not assigned PK's or FK's yet. thanks.

Cwittmaack · Jan 28, 2013

did not see attachment.

plog · Jan 28, 2013

I got that from the last database you posted. See my last critique.

Cwittmaack · Jan 28, 2013

Ok, tks will work on it all.

Table and Relationship design help (1 Viewer)

Cwittmaack

Registered User.

Attachments

plog

Banishment Pending

Cwittmaack

Registered User.

Cwittmaack

Registered User.

Attachments

Cwittmaack

Registered User.

plog

Banishment Pending

Cwittmaack

Registered User.

Cwittmaack

Registered User.

Cwittmaack

Registered User.

Cwittmaack

Registered User.

Attachments

plog

Banishment Pending

Cwittmaack

Registered User.

Similar threads

Users who are viewing this thread