Results 1 to 10 of 10
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

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

    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.

  2. #2
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    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.?

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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...

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re: Age, 10-4; my bad. getting sloppy...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    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.

  7. #7
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Quote Originally Posted by Micron View Post
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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.

  10. #10
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Include field in table or create for each usage?
    By louise in forum Database Design
    Replies: 3
    Last Post: 09-05-2015, 10:45 AM
  2. Create address, city, state, zip columns
    By tmcrouse in forum Queries
    Replies: 5
    Last Post: 06-08-2015, 12:51 PM
  3. drop down selections (country/state/city)
    By fastforded in forum Access
    Replies: 11
    Last Post: 03-26-2015, 07:40 AM
  4. Replies: 2
    Last Post: 03-18-2014, 10:15 AM
  5. City, State Zip lookup
    By garywmcp in forum Access
    Replies: 1
    Last Post: 04-24-2011, 06:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums