Normalization

[josephaldred]

I am eager for someone to look at this structure and tell me how well the normalization is.

Thanks,
Joseph

 

[HiTechCoach]

Joseph,

Since you did nor describe the process that this data must manage, we con only give you very general issues on a per table basis, not for the entire process (applicatio0n/database).

At first glance here are some of the things I see:

1) Work Orders - has repeating fields.

2) People are people. The all should be in a single table not multiple tables. IMHO, MechanicList and Clients are duplicating the dame data. They should be combined into a single table.

3) MechanicList has repeating fields

4) I would urge you to follow the The Ten Commandments of Access
- Thou shalt choose a naming convention and abide by its wisdom and never allow spaces in thy names.

a) You naming is not consistent.
b) Never use spaces or any other non-alphameric characters in object names!

5) You have field names that are reserved words. This can cause lots of issues. Example: Date

See: Access Reserved Words


Hope this helps ...

 

[josephaldred]

It does help, thanks.

By repeating fields do you mean a field that is also in another table? Like MechanicID in the MechanicList and Mech_WO tables or is the two Primary and Seconday Phone Number entries? Other than that (the same question goes for WorkOrders table) I understand what you are saying.

In putting all the people in one table, would I then just have the Airplane table connected to the WorkOrder table?

BTW since I did not mention it before, this database is for a client that wants to keep track of the planes his work repairs. He had hired someone to make something like this before, however the guy used Excel instead. I thought Access would be a lot better. So it is driven by the work orders table.

Thanks for your help,
Joseph

 

[HiTechCoach]

By repeating fields do you mean a field that is also in another table?

No. I mean in the same table.

In putting all the people in one table, would I then just have the Airplane table connected to the WorkOrder table?

The idea is that you would have one single table for people. The same table would be used to link a person are a client of as a mechanic.


To establish a relationship between tables, you will have to store the primary key from the main table in any related table as a foreign key field.

... So it is driven by the work orders table.

That contradicts what you said was the purpose for the database.

If you described the purpose of the database accurately then:

Since you said "this database is for a client that wants to keep track of the planes his work repairs". That would mean that the database is driven by the Planes. Works order are just the activity.


I would have to disagree with you that "it is driven by the work orders table."

I do agree that Access is much better for handling this type of data than Excel.

If it were me doing this project, I would never look at the Excel data until I am ready to import data. In a properly design relational; database, it will look nothing like an Excel spreadsheet. The trap with looking at the Excel data is that it may influence you to make bad design decision for your database.

Side note: I can see an Excel (spreadsheet) influence in your current design now. I am not surprised to hear you say that it is a conversion from Excel.

 

[josephaldred]

I went back and reworked it some, taking your advice into account, and trying to remember what I learned in class. I appreciate any comments.

Thanks,
Joseph

 

[HiTechCoach]

Joseph,

You are making progress.

I do not understand what you did with the phone numbers. The relating field do not look correct. Normally you would link tot he primary key for the contacts table. So for the three new table for Work orders.

Would it be possible that you post a sample of your data with some sample data in it (nothing confidential)?

 

[josephaldred]

I think I'm lost again. Are you saying that I need to link from ID_PhoneNumber to the primary key in the tbl_Contact? And the same thing for the three that are connected to tbl_WorkOrders?

The only data I have in the database is just things I have put in to test some of the forms I had before taking your advice. Nothing other than a few taps on the keys. Or do you mean from the spreadsheet?

 

[josephaldred]

Lets try this again. I made some changes, for the better I hope, I would appreciate any comments or suggestions.

Thanks,
Joseph

 

[josephaldred]

Oops . . . maybe this is better.