Relationship between two tables (what is correct?)

  • Hi, can someone help me with this basic doubt? "tnx in advance"

    I have two tables (groups, rol), I need to know what's the correct way to create a relationship

    my tables definition

    Groups_cat

    ---------------

    [b]Pk[/b] Group_iD

    Group_name

    Rol_cat

    ---------------

    [b]Pk[/b] Rol_iD

    Rol_name

    Rol_description

    the doubt is here.... What is the correct way to connect this tables?

    This?

    Rol_Group_xtbl

    -----------------

    [b]Pk[/b] RolGroup_iD

    [b]Fk[/b] Group_iD

    [b]Fk[/b] Rol_iD

    Or this?.. I'll need to delete the surrogate key and create a composite key?

    Rol_Group_xtbl

    -----------------

    [b]Pk[/b] Group_iD

    [b]Pk[/b] Rol_iD

    What is better and why?

  • Carl0s_ (5/7/2013)


    Hi, can someone help me with this basic doubt? "tnx in advance"

    I have two tables (groups, rol), I need to know what's the correct way to create a relationship

    my tables definition

    Groups_cat

    ---------------

    [b]Pk[/b] Group_iD

    Group_name

    Rol_cat

    ---------------

    [b]Pk[/b] Rol_iD

    Rol_name

    Rol_description

    the doubt is here.... What is the correct way to connect this tables?

    As I see it, there is no relationship between those tables.

    Do "roles" belong to "groups"? meaning, groups is parent and roles child?

    Is there a one-to-one, a one-to-many or a many-to-many relationshiip?

    What your ER model tells you?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Hi Paul

    Thanks for reply, this doubt is in general for "link tables",actually im work in this.

    the relationship is (role)1..N to 0..1(groups)

    I want to make role groups, as you ask.

    Imagine this Roles (adminA,adminB,Teachers) belongs to "Administrative" group.

    Rol (teachers, adminB) belongs to "Evaluate student" group, and continuous for numerous combinations

    The main idea is to create groups to contain roles, and give permissions to that groups to access some activities (one activitie has permission to some grouped pages), and not doing this role by role in a level role-page.

    My doubt isn't necesary only to solve this example, I want to know what is the correct way to do, because I have more link tables in my database, and I use the first form in example (PK surrogate, fk, fk).

    Thanks in advance Paul (sry my bad english)

  • Carlos, so you're looking for how we usually approach creating many to many tables?

    The relationship is (role)1..N to 0..1(groups)

    This would usually imply that you have a 1 to many relationship, not many to many. I assume this is just part of the language barrier.

    In this case, because your role_group_xtbl will not be containing any additional information, I would simply PK across the two ints. Depend on which direction you'll usually go (I assume your typical parameter will be role, which will then transit to find out what group permissions it has) I'd lead the table with Role_ID so you can seek directly to your target rows when you build the clustered index.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Kraig, thanks for your comment, I tried to said that, relationship 1 to many, sry my mistake, I need to improve my english.

    back to topic, I decide to use only Pk's in the link table and not an extra surrogate key. Thanks for answers 🙂

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

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