When to use one to many vs many to many in right situation?-CodePudding

i quite confuse when, or not to use one to many vs many to many. ex, user roles. in such situation many to many have advantage in reduce data size cause it just point to integer, maybe it save 1-10bytes each row, ex, senior developer char with id 7, it consume 2 bytes in smallint, instead 16 bytes. but, it makes bloat table. if such situation use many to many. why one to many should exists if many to many have the advantage? is it not always good to many to many?

Users table
id
username
password

Users_Roles table
user_id
role

Versus

Users table

Users_Roles table
user_id
role_id

Roles table
id
role

CodePudding user response：

You're prematurely optimizing. A few integers here and there is unlikely to impact your data size nor your performance. If it does, the schema can be changed later, but usually there is much bigger bloat to be concerned with.

One-to-many vs many-to-many is not an optimizing issue. It's about the relationship between the tables.

If one and only one user can have a role, use one-to-many.
If many users can have the same role, use many-to-many.

For example, if you have an admin role and there can ever only be one admin user, use one-to-many. If there can be many admins, use many-to-many. You have to decide what the relationship is between users and roles.

Note: Use bigints for ids. 4 billion might seem like a lot, but it comes up fast and one of the worst things that can happen is to run out of IDs.

CodePudding user response：

This is a data modeling question, and it's answer comes out of and is dictated by the analysis of the relationships of the entities involved. You have identified 2 entities you want to store data about, users and roles. Now describe their relationship in spoken language terms, looking at the relationship from both directions.

Can a user have more than one role? Can a role be held by more than one user? If the answer to both is yes, than it's a many to many relationship. Take the primary keys of both entities and bring them together as the composite primary key of an associative table. It may not have any attribute unless there is data about the relationship of a user/role itself that needs to be captured.

However, what if you are modeling entities of invoices and line items? Can an invoice have more than one line item? Yes. Can an instance of a line item on an invoice belong to more than one invoice? No (note I'm modeling a line item, not a product or part number as a line item could include special pricing for this invoice, color, logo, etc). So this is clearly a one to many relationship in the direction of one invoice can have many line items.

For more information, do some searching on data modeling, it will be a huge help in your database design efforts and you will end up with a better design for more efficient queries by designing the database correctly.

Looks like Schwern and I were typing at the same time :-)