Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Relationship between two tables (what is correct?) Expand / Collapse
Author
Message
Posted Tuesday, May 7, 2013 6:54 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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
---------------
[Pk] Group_iD
Group_name

Rol_cat
---------------
[Pk] Rol_iD
Rol_name
Rol_description

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

This?
Rol_Group_xtbl
-----------------
[Pk] RolGroup_iD
[Fk] Group_iD
[Fk] Rol_iD

Or this?.. I'll need to delete the surrogate key and create a composite key?
Rol_Group_xtbl
-----------------
[Pk] Group_iD
[Pk] Rol_iD


What is better and why?
Post #1450382
Posted Wednesday, May 8, 2013 8:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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
---------------
[Pk] Group_iD
Group_name

Rol_cat
---------------
[Pk] 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.
Post #1450600
Posted Wednesday, May 8, 2013 11:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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)
Post #1450707
Posted Wednesday, May 8, 2013 6:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, September 22, 2014 10:53 PM
Points: 5,383, Visits: 7,456
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1450852
Posted Wednesday, May 8, 2013 11:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 8, 2013 9:54 AM
Points: 13, Visits: 89
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 :)
Post #1450883
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse