Results 1 to 5 of 5
  1. #1
    jon_ak is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Ewa Beach, Hawaii
    Posts
    3

    Table Design Issue


    In an attempt to arrive at 2NF or 3NF, I have created several tables from a single table to avoid what would become redundant data: city table w/zip code, state table, type of work table etc. Of these, there are several one to many relationships and one, many to many relationship. Problem I'm experiencing, the city and state tables contain the primary key as there should only be one of these while the customer and job tables contain the foreign key. In a customer record entry form for instance, to record a new customer, what is the recommended method for adding the new customer as well as being able to select the city and state? beings how the primary key is in the city and state table, the error I get tells me the parent record must exist first before the child record. I realize this but am at a quandry at how to get it to work properly.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I think you should work through one of these tutorials (or both) from RogersAccessLibrary.

    Entity Relationship Diagramming

    Consolidated Widgets
    A solution is provided. Work through the tutorial following the procedure outlined.You will learn.
    And what you learn can be used with any database.

    Good luck.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the error I get tells me the parent record must exist first before the child record
    with fields like city and state, There are others who will disagree, but I would just have a single column indexed table for use by your combo's as a rowsource and forget about an autonumber primary key so you are storing the text in your table and not a foreign key. OK, not quite normalised, but practical. The list can limit what the user can choose and provides a consistency of spelling, and it is unlikely you are going to want to change the way your spell Utah or New York - if you do, then a little bit more work, but not much - a simple update query. It also means that queries searching/filtering are simpler since you do not need to include a joins in the query.

    You can take normalisation too far - what about peoples names? Mr John Smith? Plenty of Mr's about so lets have a primary key for that. Lots of John's as well, so perhaps we should have a table of first names and use a foreign key instead. And Smith, a popular name so why not have a foreign key to a table of surnames? But to use the data starts to get more complicated.

    with regards your parent record issue, I'm guessing state is the parent and city the child. If so, you need to add the state record before you add the city record - which isn't of course the way you would naturally complete an address. One option is to remove referential integrity so you can enter a city, then the state. Another is to have unbound fields on your form with some code in the form before or after update/insert events to sort it out later.

    Don't confuse referential integrity with business rules. Referential integrity says 'any city must be in a state', Business rules says 'any city must be in the right state'.

  4. #4
    jon_ak is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Ewa Beach, Hawaii
    Posts
    3
    i will certainly do this. thanks....

  5. #5
    jon_ak is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Mar 2014
    Location
    Ewa Beach, Hawaii
    Posts
    3
    The combobox is what i have been using. It works fine in the other forms I have where the record being added is the parent table and so I may have gone a bit off the deep end in creating separate tables for city and state. I have some VBA code for the "not in list" event to allow the user to add a new city and that works but since the city and state tables are both the parent in the relationship with the customer table, won't work by not creating the city and state records before the customer record... totally backwards. You are probably correct in just having a table for city names and a table for state names without having the primary and foreign keys

    As for the tables, the customer table contains the foreign key to the city and state tables. I am using referential integrity as a safeguard due to a few unwieldy, trigger happy fellow workers altering excel sheets without thinking. At least they won't be able to delete the parent record without deleting the related child records first. Am not much of a fan of cascading deletes....

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

Similar Threads

  1. form and tab control design issue
    By ShostyFan in forum Forms
    Replies: 5
    Last Post: 12-10-2013, 12:26 PM
  2. Design issue
    By Accessuser67 in forum Forms
    Replies: 3
    Last Post: 12-18-2012, 05:41 AM
  3. Design Issue: Custom Property?
    By Stan Denman in forum Database Design
    Replies: 2
    Last Post: 03-30-2012, 11:11 AM
  4. Replies: 1
    Last Post: 03-06-2011, 06:21 PM
  5. Database Design Issue
    By joekiteire in forum Database Design
    Replies: 6
    Last Post: 02-26-2009, 04:53 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