May 7, 2013 at 6:54 pm
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?
May 8, 2013 at 8:20 am
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.May 8, 2013 at 11:17 am
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)
May 8, 2013 at 6:19 pm
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.
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
May 8, 2013 at 11:06 pm
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