Post reply

(Newbie) SQL Table Design / Relationships

  • April 26, 2017 at 9:23 am

    #329918

    First of all, I'm completely new to SQL, literally the first time I've ever tried to create a database with it.
    I'm having a little bit of trouble working out the best / most effecient design for the tables.
    Below are the two idea's I have: The idea is that the Site information, NML information and Problem groups will all be linked to the Username.

    Can anyone have a look over these and let me know what the best way to design the database would be? I thinking more around the relationships between all the tables.
    I'm really not sure if there is a need for the intermediate tables or not.

  • April 26, 2017 at 10:09 am

    #1940037

    A few quick questions to consider:

    • What is the reason to use surrogate keys rather than natural keys?
    • Should relationships between tables use different data types?  (User.UserKey int / UserSite.UserKey varchar)
    • Is there a reason most column names are spelled out while several columns in User are abbreviated and use non-alpha characters?

  • April 26, 2017 at 2:57 pm

    #1940109

    The thing to think about for each relationship when deciding if you need the intermediate tables, is the relationship really one to many or many to many.

    In the top diagram, you are saying "a Site can have multiple Users belonging to it, and each User can only belong to 1 Site"
    In the bottom diagram, you are saying "a Site can have multiple Users, and each User can belong to multiple Sites"

    Similarly for ProblemGroup
    In the top diagram, you are saying "a ProblemGroup can only have 1 User belonging to it, and each User can belong to multiple ProblemGroups"
    In the bottom diagram, you are saying "a ProblemGroup can have multiple Users, and each User can belong to multiple ProblemGroups"

    Similarly for NML:
    In the top diagram, you are saying "a User can have multiple NML rows, and a NML row can only belong to 1 User"
    In the bottom, you are saying "a User can have multiple NML rows, and a NML row can belong to multiple Users"

  • April 27, 2017 at 4:29 am

    #1940172

    Thanks for the advise chaps, I've decided on the following.

    Rob Buecker - Wednesday, April 26, 2017 10:09 AM

    A few quick questions to consider:

    • What is the reason to use surrogate keys rather than natural keys?
    • Should relationships between tables use different data types?  (User.UserKey int / UserSite.UserKey varchar)
    • Is there a reason most column names are spelled out while several columns in User are abbreviated and use non-alpha characters?

    Very new to SQL so getting my head around the very basic things still.
    - Since the UserNames / ProblemGroup / NMLName are always unique, changed to use these as Primary keys, so no longer need the surrogate keys.
    - That was an error in my design, corrected.
    - Not really, just what I'd written down in my notes.

    Chris Harshman - Wednesday, April 26, 2017 2:57 PM

    The thing to think about for each relationship when deciding if you need the intermediate tables, is the relationship really one to many or many to many.

    In the top diagram, you are saying "a Site can have multiple Users belonging to it, and each User can only belong to 1 Site"
    In the bottom diagram, you are saying "a Site can have multiple Users, and each User can belong to multiple Sites"

    Similarly for ProblemGroup
    In the top diagram, you are saying "a ProblemGroup can only have 1 User belonging to it, and each User can belong to multiple ProblemGroups"
    In the bottom diagram, you are saying "a ProblemGroup can have multiple Users, and each User can belong to multiple ProblemGroups"

    Similarly for NML:
    In the top diagram, you are saying "a User can have multiple NML rows, and a NML row can only belong to 1 User"
    In the bottom, you are saying "a User can have multiple NML rows, and a NML row can belong to multiple Users"

    Thanks for this Chris, really cleared things up.
    If I'm understanding things correctly here now, in the attached diagram:
    - A site can contain multiple users, but a user can only have a single site. (One to Many)
    - An NML can contain multiple users, but a user can only be part of a single NML. (One to Many)
    - A ProblemGroup can have multiple Users, and each User can belong to multiple ProblemGroups.(Many to Many)

  • April 27, 2017 at 7:19 am

    #1940193

    Mycaelis - Thursday, April 27, 2017 4:29 AM

    Thanks for this Chris, really cleared things up.
    If I'm understanding things correctly here now, in the attached diagram:
    - A site can contain multiple users, but a user can only have a single site. (One to Many)
    - An NML can contain multiple users, but a user can only be part of a single NML. (One to Many)
    - A ProblemGroup can have multiple Users, and each User can belong to multiple ProblemGroups.(Many to Many)

    yes, you've got it!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply