Results 1 to 6 of 6
  1. #1
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71

    Proper way to store Names

    I have several instances similar to this:



    Lets say I need to store the instructor of a class. This instructor 90% of the time will be somebody who's listed in my "People" table. In which case my instructor field would hold the database ID of that person and the user would select it with a combo box.

    What about the other times when the person is not (and should not be) in the people table?

    I've gotten around this by having a text instructor field that the user can either choose from a list OR type in their own name.

    However, if the instructors name changes in my People table now all those records holding that instructors name in a Course table are incorrect.

    Is there a better way to handle this situation? Some method of storing a database ID OR a custom name?

    Thanks

    P.S. I don't need specifics, just a broad idea. I was thinking One field for ID_Person, then 2 more for Last Name and First Name. Handle the display on Forms/Reports. Good Idea? Bad Idea?
    Last edited by trb5016; 08-04-2010 at 10:23 AM. Reason: Clarification

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,368
    your concern about the instructor's name changing shouldn't exist. why would you be holding the instructor name in the Course table? hold the instructor ID so no matter what the name changes to, everything is correct.

    what is the difference between your People table and the other table that might hold instructors? perhaps there is a way to combine those into one table and have an indicator field to differentiate "People" from whatever else.

  3. #3
    trb5016 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    Yea, I don't want to store the Instructor's name in the Course table, just their ID. But sometimes the instructor wouldn't be in the people table, and they just need to be entered on the fly by the user.

    I wouldn't want to store the the "other" instructors name in the people table because in my database the People table holds much more information than just the name. I could use a flag to filter it out, but there's going to be a bunch of blank fields for all the "other" instructors.

    I thought of making another table for "Other People" that holds just the names and an [ID_Other Person]. The combo box could then use a union query to pull from People and Other People. I'm concerned because Union Queries seem to equal bad design.

    The alternative is my People table should be split up so that ID_Person, First Name, Last Name are in one table, and the other information is in a 1 to 1 relationship with all the other information I need.

    The issue with that is it seems silly that 90% of the names in the Names table would have 1 to 1 records in that other table.


    GAH I keep going back and forth on what is better. I really think it's bad juju to store the name in the courses table rather than an ID, but that necessitates adding EVERY instructor to a table with an ID.


    Advice? Thanks for your thoughts! (And sorry for the text wall)

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Based on your parameters (that the person should not be in the people table), that sounds like a reasonable method. I assume this is some sort of one-off situation.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    trb5016 is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Dec 2009
    Location
    Pennsylvania
    Posts
    71
    Quote Originally Posted by pbaldy View Post
    Based on your parameters (that the person should not be in the people table), that sounds like a reasonable method. I assume this is some sort of one-off situation.
    Well, it does occur a few times in this database as a whole (50 plus tables), there's 2 I can immediately think of with the potential for more as more features are added. This would require me adding the "extra" fields to each table where this occurs.

    It seems like there's at least 4 ways of handling this and I can't decide which is best!

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    One way I help decide which way to use when there are multiple ways to do it is to also think about how the form design is going to work for updating the data. If you complicate the structure too much and end up designing a bunch of work-arounds in your form design, you end up chasing down the data all the time to fix what otherwise could've been simplier to design your forms around.

    Sometimes it's easier to work with a 'flatter' table structure (and not normalizing all the data - ie. survey type data) and other times it's easier to go the relational route.

    When I was working with millions of records in a relational structure, I found that it was actually better to non-normalize it to some degree and put 'grouping' types of fields in the main table and avoid linking multiple relational tables in my totalling queries and get a much faster query execution time. (each table you have to join in a query costs you execution time.) By putting certain grouping fields (and even storing certain total values in the table itself), I could cut down a 20 minute query into seconds and it only cost me a function or two in the code to update the data.

    I go by the rule that there really isn't 1 set 'golden' rule on when you should/should not normalize. I used to think flatter type structures should always be normalized but after having to go through a ton of work-arounds to get the data forms/queries to work nicely, I now also look at what's really best all around and factor in coding time, form design, and query execution as well.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-24-2010, 09:19 PM
  2. How do I get dlookup values to store in table
    By rpmyhero in forum Access
    Replies: 1
    Last Post: 11-25-2009, 05:57 AM
  3. Email with proper page orientation of Report
    By Robert M in forum Programming
    Replies: 1
    Last Post: 08-17-2009, 10:28 AM
  4. Pizza store database help!
    By zagorette in forum Database Design
    Replies: 2
    Last Post: 06-30-2009, 10:47 AM
  5. Replies: 0
    Last Post: 09-25-2006, 03:42 AM

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