Results 1 to 3 of 3
  1. #1
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218

    Database table design question

    I have a database I'm building which is in 3rd normal form. After adding real data to the tables and doing lots of testing, I wonder if I've broken it apart too far and perhaps have 1 or 2 too many tables.



    The relationships look like this:

    Click image for larger version. 

Name:	relationship2.0.png 
Views:	40 
Size:	46.8 KB 
ID:	22520

    Specifically, for tblCustomer I have 4 separate lookup tables, each with unique records in them. Those are tblRank, tblOrganization, tblShopName, and tblOfficeSym. I am wondering if I should consolidate tblShopName and tblOfficeSym into one table, or if its good to keep them separate?

    An Organization is like a Company. Within that organization are shops or workcenters. For an example, within Civil Engineering organization there can be the Plumbing shop, the Electric Shop, etc. A shop almost always has an office symbol which is a 5 letter code. However there are many shops which currently do not have shop names, but have office symbols. The reason for this is simply because I do not currently know what the shop name is, but I intend to find out that information. Given the extremely large number of shops, this is going to take some time.

    Additionally, although rare, some shops do not have office symbols. Not that I don't know the office symbol, but that they simply don't have one. In which case, I repeat the shop name in tblOfficeSym to make it easier for users who don't know any better. For an example, we have a gas station. The company is the company of the gas station, the shop name is the name of the gas station because technically the gas station is a workcenter. And then I repeat that in tblOfficeSym because I figure the user might be stupid and might search in one or the other field, and I want to make it easy for them to find what they're looking for.

    Now, I have a couple different search forms which contain cascading combo boxes. A user selects Organization, and then Shop Name and Office Symbol combo boxes are filtered based on tblCustomer.OrganizationFK. As you know, this only works if there is an existing record for a customer with that Organization and Shop Name/Office Symbol in tblCustomer. So if adding a customer who has a new shop name / office symbol, that shop name / office symbol wont show up in the combo boxes. However, this problem is resolved by adding a OrganizationFK field in both tblShopName and tblOfficeSym and linking them to tblOrganization.OrganizationPK then filter the combo boxes by tblOrganization instead of tblCustomer.


    All that said, I am not sure whether I should delete tblOfficeSym and add OfficeSym as a field to tblShopName then add OrganizationFK to that table and spend some time copying data over to the new location, or if I should leave it as is and add OrganizationFK to both tables.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    For the parts of the structure you are talking about, I don't see much wrong with it.

    Since any given shop has only one symbol associated with it, then you could (and probably should) combine tblShopName and tblOfficeSym into one.

    The way you have it, a customer is in one organization, and has only one shop/symbol. There is no explicit relationship between shop and organization, so you can't put OrganizationFK into ShopName. If there IS a relationship (i.e. shops ARE relatiod to organizations), then your diagram isn't right - but how to fix it will depend on exactly what your relationships are.


    Can you provide a bit more detail on how your relationships work?

  3. #3
    ittechguy is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Aug 2015
    Posts
    218
    Thanks for your reply.

    I ended up creating a relationship between tblShopName and tblOrganization. Also between. TblOfficeSym and tblOrganization. I think I'll leave it like that for now until I get around to merging shop name and officesym. I agree they should be in one table, but it seems to work with them separate. I suppose how it is now there is less null values for if I don't know the shop name. I'll try to explain the relationships tomorrow.

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

Similar Threads

  1. Possible question in the design of a database
    By AzizSader in forum Forms
    Replies: 3
    Last Post: 04-21-2014, 11:17 PM
  2. Database design question
    By audmkamp in forum Database Design
    Replies: 2
    Last Post: 01-21-2013, 01:48 PM
  3. Database design Question
    By access2day in forum Database Design
    Replies: 1
    Last Post: 05-03-2012, 10:15 AM
  4. Database design question
    By udigold1 in forum Database Design
    Replies: 3
    Last Post: 03-23-2012, 02:20 PM
  5. Database Design Question
    By AccessNewBiegr in forum Access
    Replies: 2
    Last Post: 02-08-2011, 08:22 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