Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Relationship between two tables (what is correct?)


Relationship between two tables (what is correct?)

Author
Message
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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?
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
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.
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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)
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5695 Visits: 7660
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
Carl0s_
Carl0s_
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 91
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 Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search