4

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?

Improve this question
4
  • Are clusters "created" somehow? Having them in a separate table enables the FK to check existence of the associated cluster. In your case "associating" some user_id with a cluster_id can create new cluster if "wrong" cluster_id is inserted.
    – jkavalik
    Sep 22, 2015 at 10:46
  • Thanks for the comment. They are created periodically and automatically as a result of machine learning algorithm. The purpose is only to group users.
    – Kobe-Wan Kenobi
    Sep 22, 2015 at 10:56
  • 1
    In that case the ability to create a cluster just by associating it with some user and removing by deleting all associations are actually positive effects.
    – jkavalik
    Sep 22, 2015 at 11:06
  • Thanks, that conformation is very valuable to me. You have +1.
    – Kobe-Wan Kenobi
    Sep 22, 2015 at 11:14

2 Answers 2

Reset to default
0

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).

Improve this answer
3
  • Thank you for your answer, the example you've showed is what I had in mind. The thing is, the clusters are result of machine learning algorithm, so they only purpose is to show which users belong together. Also, you could have a query that will return all the users from the cluster. So that is the reason why currently it doesn't (and probably won't have) attributes. The only thing besides that is that clustering is performed periodically, so users may change clusters during time. Additionally, new clusters may be added. But you cannot add names automatically to such clusters.
    – Kobe-Wan Kenobi
    Sep 22, 2015 at 10:55
  • 1
    Would there ever be any desire to name or add notes to a cluster later in its life, perhaps during some analysis by a human operator? Or might you want to add any auditing information (creation time and so forth)? Otherwise yes if a cluster truly has no properties other than existing and having an ID to link to, this format is fine and an extra table is unnecessary.
    – David Spillett
    Sep 22, 2015 at 15:34
  • Well, the application is very dynamic, so clustering happens relatively often and previous clusters become not relevant, so I guess one table should work. Thanks again.
    – Kobe-Wan Kenobi
    Sep 23, 2015 at 7:28
1

The 'Right' thing to do depends.

If there's any chance the cluster might end up having attributes in the future, then yes you should have a join table.

Otherwise, if you are trying hard to normalize your data, then you should have a join table, if you are more concerned about minor size/performance boosts then you could skip the extra table. Personally, since I don't have a time machine to see the future, I'd make the extra table anyway.

Improve this answer
1
  • Thank you for your answer. Well, as you said, it is true that you cannot predict changes like that in many cases, but I think that in my case it won't have attributes since it's only purpose is to group users.
    – Kobe-Wan Kenobi
    Sep 22, 2015 at 10:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.