Check Tables

[KadeFoster]

Could some one please check over my tables and suggest what i can do to clean them up and relationships. I have an idea but i am lost on the path to achieve it. Gotta get the foundation totally right before i try for the forms.

I am using access 2010.

 

[Pat Hartman]

All of the tables use "ID" as the name of their primary key. That provides no useful information and makes it difficult for anyone looking at the schema to identify foreign keys.
1. Change all the "ID" fields to meaningful names such as - CrewID, HandoverID, etc.
2. Change the names of all foreign keys to match the name of the PK they reference whenever possible. In the rare cases where more than one FK in a table points to the same PK or for a self-referencing relationship, use good judgement. For example, in an Employee table you'll have EmployeeID as the PK and SupervisorEmployeeID as a FK.
3. Change any column name that duplicates the name of a function or property to a name that will not cause conflict in VBA - HandoverDate, ScalingHours.
4. ALWAYS enforce RI. I see some of your relationships seem to be meaningless because they don't reference a PK and they can't enforce RI.
5. Select Cascade Delete whenever appropriate.

 

[KadeFoster]

Yeah i thought my DB was a bit of a mess, just going through it now to fix up lookups and relationships as it looks complicated and messy to me and i got no idea lol

 

[Pat Hartman]

Do not use lookups at the table level. They will cause nothing but trouble with queries and VBA. Use lookups ONLY in forms.

 

[KadeFoster]

So don't make a table with the options in them? then link to that table?

 

[KadeFoster]

Ok I have redone my tables and i think they are looking alot better then what i had before. I am still uncertain what to do with the Supervisor Field on the Handover Info Table and how to relate it to the employee details.

Also I am wondering if I should remove the Forklift info from the jobs table and make it its own table or include it into the Units table. Also if i have a JobType as Scaling how do i get that to relate to my scaling table? Do i have to include all the scaling Fields into the jobs table?... Will be like splitting off the forklift info from the Jobs table as well.. any suggestions...

 

[Pat Hartman]

You still have missing relationships, and incorrect names. You also have 1-1 relationships which should probably be 1-many.

 

[KadeFoster]

OK I have fixed a fair bit and i think i am on track now... let me know what you think...

Just ignore the forms that are in the DB as I am playing around with them to work out what things do and trying to work out how to get them nice and neat and professional looking... wish there was a program for designing them

 

[gemma-the-husky]

kade - out of interest, you really are going about this the best way. getting the table design right is the main thing to facilitiate swift and harmonious development

there is often a bit of "step-wise refinement" though. when you start to develop, you have to go back, and revisit something.

good luck with your project. I am sure Pat will help you get your design spot on.

 

[KadeFoster]

Thanks, well the basis of a DB is the tables that store the data so i really want to try and get them all sorted in the best way. Also i don't want this to fall over at work and be totally useless. I am struggling a bit but i think i am getting there. Its hard just using forums, chatting to some one would be much easier. I am kinda stuck at the moment and not sure what to do in regard to a couple of relationships i need and the forms i need to create for the data entry. Just hoping i have my tables set up right...

 

[Pat Hartman]

I specifically said to not use lookups at the table level and you have them everywhere. These will come back to bite you as soon as you start creating queries or writing VBA. Lookups belong on forms.
Why is job not related to anything but lookup tables?
You are also inconsistant with your names. For example, you have EmployeeID as the PK and EmployeeName as the FK. This is extremely confusing especially since you have a field named EmployeeName in the employee table. Get rid of the lookups. EmployeeName should be split up anyway. It is always a problem to mush fields together. You'll have trouble writing letters to employees because you'll have to "decode" the mushed name. You'll also have trouble sorting. If you tell the users to enter the names as Smith, Jane - I can virtually guarentee that after a short time you'll have names going both directions and you won't be able to sort it out with code because there'll be no consistancy.

 

[KadeFoster]

Ok, I got confused at what you meant to i did what i did... lol. So if i use the form for the drops downs is there an easy way to update them? So making a table with answers for the form combos isn't the way to do it? Just manually input the options in each of the things i require. Jobs i am still trying to figure out how i want to relate that... One to many to HandoverID as there will b multiple jobs per handover. Will work on it now and fix up my mess.

 

[VilaRestal]

So making a table with answers for the form combos isn't the way to do it? Just manually input the options in each of the things i require.

I don't think that's what Pat meant or at least I hope it wasn't. I totally disagree if it was: having lookup values in tables has a number of advantages 1) you can enforce referrential integrity 2) its a centralised location to maintain them 3) you don't have to rebuild a front end to deploy changes. In fact having value list comboboxes to set the values of fields is generally bad practice except for the most simple and static lists.

However, I haven't got Access 2010 here so I can't comment on the design (Kade PM'd me and asked me to look).

But I did just want to say, don't get rid of the lookup tables.

I think what Pat meant was don't set comboboxes up at the table level. Still the field relates to a table but the comboboxes that lookup that table should be done at the form level. That way there's no possible conflicts between the two.

 

[KadeFoster]

Ok, so just at table level just have the fields related, and then at the form level put in the combo for that relationship selection.

 

[VilaRestal]

Exactly.

I think it's unlikely you'd encounter problems having the comboboxes set up at the table level but you could and you're not really saving yourself work - you've got to set it up one way or the other and you can easily copy and paste a combobox already setup for a particular field from one form to another.

 

[KadeFoster]

OK i totally understand now, will fix that up. Also i am having a problem with relationships as I'm not sure what tho relate a field to. I am try to create a Handover between 2 different groups of people. Each handover can have multiple jobs on it. Not sure weather to relate the jobs to the date or the handover id? I was reading that date is not the best thing to relate to.

 

[KadeFoster]

So you cannot view my DB? as its a different version?

 

[VilaRestal]

Yeah sorry.

If you're not using attachment fields or multivalue fields then you should be able to Save As Access 2007 Database and upload that.

 

[KadeFoster]

I don't have that option, only able to save it as a access 2002-2003, access 2000

 

[KadeFoster]

Hmm cannot save it as and earlier version hmm will have to rebuild it i guess