Data Model Help. Create a City/State table? or Just include field in each table?

[CharissaBelle]

I am working on creating my first data model and ended up with a simple but complex question.



I can post my narrative if necessary, but for this question I feel it's overkill.

I have Vendors, Drivers, Post Offices, Trips, Drug Screen Clinics, etc all have states and cities they are located in. Should I make a single Locations table that lists the City and States? I will need to search through the data by state or city at some point.

[CharissaBelle]

Oh, I also wanted to ask the same thing about phone numbers. Should I keep separate phone number tables for each entity? Such as Driver Phone, Vendor Phone, Drug Screen Phone etc.?

[Micron]

As a general rule, you should not be repeating data in various places. The record id of a City/State table would be what you'd have in any table that needs it. You could split cities from states so as not to repeat city names, but it's seldom necessary or advantageous to go to that degree of nomalization IMO. Phone numbers? I don't see them going into their own table at all. That data is relevant to the driver (or whatever) and should appear along with the other driver attributes (FName, LName, Age, Gender, etc.).

Maybe you should research normalization and take a look here at the table design topics in the left nav pane http://www.fmsinc.com/free/newtips/primarykey.asp
If you are not aware of naming conventions and reserved names, I recommend researching those topics as well. The knowledge can save you a lot of headaches later on.

[orange]

Post the narrative, and the evolving model.

Further to Micron's comments, you might consider storing DateOfBirth (DOB) rather than Age--since Age changes every day and year...

[Micron]

Re: Age, 10-4; my bad. getting sloppy...

[CharissaBelle]

I'm using the Entity-Relationship Diagram method that orange sent the info on. Thank you! Roger's Access Blog is really good. I'm very much in the process and I have a long way to go, but this is where I'm at so far. I will be creating relationships today. I've done everything in Google Docs. Here are the links:

Narrative:
https://docs.google.com/document/d/1...it?usp=sharing

Entities & Attributes:
https://docs.google.com/spreadsheets...it?usp=sharing

Thank you for helping me on this.

[CharissaBelle]

Phone numbers? I don't see them going into their own table at all. That data is relevant to the driver (or whatever) and should appear along with the other driver attributes (FName, LName, Age, Gender, etc.).

Each driver could have 3 or 4 phone numbers. Each Post Office could have 8 or more phone numbers. I figured I should have a separate table for those to go into. Just not sure if I should separate between driver phone numbers, post office phone numbers, vendor phone numbers, etc.

[Micron]

Well when you put it that way a phone number table makes sense if you have a field to tie it to the "owner" and maybe a description field. But one table methinks.

[orange]

I just looked at your narrative and it is a good start. I think you should step back and adjust it to remove a certain degree of familiarity with the subject and make it more factual ( something that could be given to a developer). This will help identify details and context that you know, but others may not.

It seems to me from reading your info that a Contract:

A contract is a legal document made between our company and a third party to perform particular service(s). A service typically involves a person (driver(s)) making 1 or many trips according to a schedule with specific dates and times to specified terminations and way points.

Each trip has a trip number, postal trip number, trip rate, title, show up time, show up Post Office, time zone, multiple leave/arrive times, multiple post offices, door numbers, special schedules like for holidays or certain days of the week, notes, and equipment instructions.

This is a little bit like I call "jargonese". It's an excellent start, you know the definitions of these, but others do not. So each should be described, possibly with examples, as you proceed. In fact, it is a good strategy to review/solicit comments/adjustments from colleagues to insure that the narrative and evolving model and database are a true representation of the business, the things involved and the interactions of all. All of this becomes the center of your communications, training and documentation. This will be important to users, operators, managers and anyone doing maintenance or adjustments down the road.

Be cautious with Arrival/Leave Time. Be explicit ArrivalTimeStamp, LeaveTimeStamp (or whatever makes sense in your environment). These are/should be separate fields in your table(s) representing different things. One fact, one field. When you get to field names, use alpha characters and no embedded spaces nor special characters (things like #@!*%$^...) it will save you frustration with syntax errors.

Anyway, you're off to a good start. These comments are meant as constructive criticism, and remember that all I know about your set up is what you have written. So if I have misunderstood some aspects, set us straight.

Good luck with your project.

[CharissaBelle]

Thank you for taking a look. And thank you VERY much for the constructive comments. I am definitely going to take a step back and see what I can do to remove "jargonese". I'm going to go ahead and mark this thread solved.