I have tried personally column-based relations a great deal during my projects like:
CREATE TABLE `user` ( id INT AUTO_INCREMENT PRIMARY KEY, usergroup INT ); CREATE TABLE `usergroup` ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) );
however, at the office it appears many people get it done using table-based relations such as this:
CREATE TABLE `user` ( id INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE `usergrouprelation` ( userid INT, usergroupdid INT ); CREATE TABLE `usergroup` ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) );
and i'm wondering here do you know the benefits and drawbacks of both approach? And what's the state term with this?
The associations will vary.
Inside your first example, a 1 to a lot of relationship. One group might have many customers. (a person are only able to maintain one group)
Inside your second example, a many to a lot of relationship. Many groups might have many customers. (a person could be in several group and groups might have several user)
This is the distinction between the 2, it is common practice to make use of medium difficulty table to interrupt up a many to a lot of relationship.
You will find no general pros or cons about this. I'd call your "column based relationship" single:d relation as well as your "table based relationship" a n:m relation.
1:n means every user could be associated with zero or 1 user group and every user group could be associated with many customers.
n:m means, every user could be associated with zero to a lot of user groups and the other way around.
For those who have many-to-many relationship you have to go second way. But when you've one-to-many or many-to-one relationship you are able to choose any one of two variants (but the second reason is more expanding).
See more: Database relationships