Results 1 to 9 of 9
  1. #1
    dbd's Avatar
    dbd is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    5

    Question Relationship Decisions for Day Spa DB

    Hi,

    I recently (2 days ago) picked up Access/DB design purely because I have other more pressing work to do, and procrastination makes doing anything else a better decision that what you should be doing...
    With that out of the way, I've tried to make a database for a "Day Spa" (think saunas, etc...), and have some questions regarding my design, specifically around Normal Forms.



    I believe I've hit 1NF.

    With regard to 2NF, I'm unsure if the last two columns of the Members table meets that criteria:


    The idea behind them being if a member signs up as part of a corporate plan, we hit the "Yes" tick-box, and then select the company from the drop-down list of companies acceptable (lookup to the Companies table)... But, should this be on the Members table, or should I shift both columns to a separate table entirely? One that just has MemberID, CorporatePlan, Company?

    In a similar vein, for the Interests table, I wanted new users to be able to select some interest they'd have, like using the Sauna and Pool, while others might just want the Steam Room:

    With the table though, is it necessary to have a unique key created by Access (InterestID), or should I just use MemberID and Interest as a composite key? Though, then there'd be nothing else in the table, just the composite keys...

    I don't think I have any 3NF issues...? Maybe the Company drop-down in the Members table breaches 3NF? I'm not sure. It doesn't explicitly rely on the CorporatePlan tick-box, except logically.
    The definitions elude me ever so slightly!

    Thanks

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Currently you have not Enforced Referential Integrity between Related Tables.

    You would currently be able to add data in the related tables without there being a Parent Record in the Members Table

    It is also recommended that when you name objects you give them their respective prefix's

    ie tblMembers vice Members

  3. #3
    dbd's Avatar
    dbd is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    5
    Wow, I forgot about that entirely! Thanks mike60smart.
    I've gone ahead and added the referential integrity, and updated the tables accordingly with the "tbl" prefix.


  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It appears from the second image in your first post that you have made some fields LOOKUP FIELDS.
    This is strongly NOT recommended. See The Evils of Lookup fields

    Instead, you should use Comb Boxes on a form.

    Apologies if I misread the image....

  5. #5
    dbd's Avatar
    dbd is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Interesting read. That is indeed a lookup field, where it allows you to select a company from the table "tblCompanies". The thought being, you should only be able to select a company that is already entered in the Database; a corporate plan needing to be negotiated prior to a user from the company signing up under it for example.

    In order to do that kind of business logic, are you saying it would require a Form? Does that mean entering data into the Table directly would no longer be an option, as the required logic for that column is only accessible in Access' forms?

    Apologies if this is a stupid question, very new to all this!

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In order to do that kind of business logic, are you saying it would require a Form? Does that mean entering data into the Table directly would no longer be an option,
    I NEVER allow users to have access to the tables (raw data). It is too easy to change the wrong field, can't verify entry, can't do calculations if necessary...

    Even for a really simple dB for myself, I ALWAYS create a form.



    BTW, in "tblCreditCards", you have two fields named "Number" and "Type". These are reserved words in Access and shouldn't be used for object names.
    See Problem names and reserved words in Access

  7. #7
    dbd's Avatar
    dbd is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Again, had no idea haha. Appreciate the link. I've gone ahead and prefixed both with "CC" so there's no collisions there, and couldn't see any others.
    I'll go about learning Forms shortly and see if I can work in the logic there as opposed to using a Lookup in the table directly

    With regard to my original query, do you think it's safe to assume I can keep those two columns inside the tblMembers table (when considering the Normal Forms)? Part of me thinks it's fine, the other part has this nagging "you're doing it wrong" whisper.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, here we go.......

    The idea behind them being if a member signs up as part of a corporate plan, we hit the "Yes" tick-box, and then select the company from the drop-down list of companies acceptable (lookup to the Companies table)... But, should this be on the Members table, or should I shift both columns to a separate table entirely? One that just has MemberID, CorporatePlan, Company?
    It looks like it would be ok to leave the 2 fields in the Members table. Can a member join using 2 different Corporate Plans from 2 different companies? Does/can a company have more than one plan (Bronze, Sliver, Gold)? Who pays for the plan? If I walk in off the street or my company doesn't have a plan, how does one join? Do you leave the company field blank? Or do you enter "Private Person"? Remember, a blank field means the value is unknown or missing.


    In a similar vein, for the Interests table, I wanted new users to be able to select some interest they'd have, like using the Sauna and Pool, while others might just want the Steam Room:
    One member can have many interests and one interest would be for one member. So you have a 1-to-many relationship. These tables look good.


    So an advantage of using forms instead of entering/viewing data from tables, is that you can use a main form with sub forms/list boxes to view ALL of the data for ONE member at the same time from many tables/queries.



    Reading time!!!!

    What is Normalization?
    =======================
    What Is Normalization, Part I: Why Normalization?.............. http://rogersaccessblog.blogspot.com...on-part-i.html
    What Is Normalization, Part II: Break it up......................... http://rogersaccessblog.blogspot.com...n-part-ii.html
    What Is Normalization: Part III: Putting It Back Together...... http://rogersaccessblog.blogspot.com...-part-iii.html
    What is Normalization: Part IV: More Relationships............... http://rogersaccessblog.blogspot.com...n-part-iv.html
    What Is Normalization: Part V: Many-to-Many Relationships.. http://rogersaccessblog.blogspot.com...on-part-v.html


    Entity-Relationship Diagramming
    ==============================
    Entity-Relationship Diagramming: Part I.... http://rogersaccessblog.blogspot.com...ng-part-i.html
    Entity-Relationship Diagramming: Part II... http://rogersaccessblog.blogspot.com...g-part-ii.html
    Entity-Relationship Diagramming: Part III.. http://rogersaccessblog.blogspot.com...-part-iii.html
    Entity-Relationship Diagramming: Part IV... http://rogersaccessblog.blogspot.com...g-part-iv.html


    The Normal Forms (relating to tables)
    =========================
    The Normal Forms: Introduction........................ http://rogersaccessblog.blogspot.com...roduction.html
    The Normal Forms: First Normal Form (1NF)......... http://rogersaccessblog.blogspot.com...-form-1nf.html
    The Normal Forms: Second Normal Form (2NF)..... http://rogersaccessblog.blogspot.com...rmal-form.html
    The Normal Forms: Third Normal Form (3NF)......... http://rogersaccessblog.blogspot.com...rmal-form.html
    The Normal Forms: In a Nutshell ....................... http://rogersaccessblog.blogspot.com...-nutshell.html

  9. #9
    dbd's Avatar
    dbd is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2020
    Posts
    5
    Quote Originally Posted by ssanfu View Post
    Do you leave the company field blank? Or do you enter "Private Person"? Remember, a blank field means the value is unknown or missing.
    Uh-huh, I hadn't considered that. The plan was originally to just leave it blank if the Yes/No tick-box was left unchecked, meaning they weren't a part of a corporate plan, but I hadn't thought that a blank field may be considered a "missing" value. I may need to give some more thought as to how to structure that, or just go with the "Private Person" or "Individual" moniker route.

    Those links will keep me occupied for some time, greatly appreciated!

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

Similar Threads

  1. one to many relationship
    By mountainclimber in forum Access
    Replies: 8
    Last Post: 08-04-2015, 10:53 AM
  2. Replies: 3
    Last Post: 03-19-2015, 05:26 PM
  3. Replies: 1
    Last Post: 12-06-2014, 12:49 PM
  4. Database that gets more specific with decisions
    By thevaik in forum Database Design
    Replies: 2
    Last Post: 07-14-2013, 07:35 PM
  5. Many to Many relationship ???
    By Sebbers in forum Database Design
    Replies: 1
    Last Post: 02-24-2013, 03:55 AM

Tags for this Thread

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