Many-to-many relationship when one entity is without attributes

database-designmany-to-manyrelational-theory

I'm interesting in mapping many-to-many relationship to relational model.

I understand that the basic approach is to create a third table (associative entity) that connects the entities in many-to-many relationship. But what happens if one of the entities doesn't have attributes? Do we really need a third table?

For example, let's consider entities user and cluster. One user can belong to multiple clusters and one cluster can contain multiple users. And we could create a third table such as "belongs-to". But do we really need that if cluster doesn't have any attributes? Could we, in that case, put the user_id key as foreign key in cluster table and consider that pair (user_id, cluster_id) as a composite key?

I see no gain of creating another table. If cluster had attributes it wouldn't make sense to repeat all the information for each cluster. But if it doesn't have attributes than the same amount of information is saved in both cases, creating a separate table or putting the user_id key in cluster table.

I'm confused, what is the right thing to do?

Best Answer

If the cluster really has absolutely no attributes then no, you don't need a separate table for these entities. The following would do:

User            UserCluster
====            ===============
UserID (PK) --> UserID (FK, PK)
UserProp1       ClusterID (PK)
UserProp2
...
UserPropN

But it is unusual that an entity has absolutely zero properties: they usually have at least have a human friendly name for display purposes. Of course you could make the cluster's ID the same as the name, but as names can change it is usually not recommended that you do this (where possible the primary key should be a value that is immutable during the existence of an entity).

Related Question