1

I't s a Database related Entity Relationship (ER) doubt: Better to have a single table storing A,B,C as a unique identifier or a Relationship. O better to split it up to three tables having then A,B - B,C - A,C

The result should be the same , having identified uniquely an A,B,C triple.

But what's could be more performant & practical in the long term, thinking on how to retrieve and manage data in a flexible way. Thinking also to frameworks and languages' ORM

Edit: (additional info)

  • A can have many features B
  • B (feature) can be used across many A
  • B has many options C
  • C (option) can be shared across many B

but at the end I need to preserve the information that a set of option C1 that have a parent B used by A1 is different from a set of option C2 that have the same parent B but used by A2

so both relation will fulfill this requirement: it's just a matter of what's more usable and convenient/performant.

  • "both relation will fulfill this requirement: it's just a matter of what's more usable and convenient/performant." Yes, exactly. The problem is, we don't know anything about your use or performance requirements, or the data. Without which, this can't be answered (both are potentially valid based on the context). – user82096 Oct 28 '16 at 8:44
  • To be honest, your examples are a bit abstract to me. What I deduce however is that you have features B which can have options C. So what I would think is that the relations would be: you make a table D : B->C and and then have the relation D in your A table or a table : A->D – Pieter B Oct 28 '16 at 13:18
  • Could you at least provide a ER diagram, however crude? – Tulains Córdova Oct 28 '16 at 13:29
3

I think the two models you propose represent very different things.

A single table keyed on (A,B,C) represents that this unique combination is valid / has data attached to it. Looking up a row and interpreting any data based on A and B only would be an error. If you can predict the valid values of C based on A and B, then it is not part of the key; if there's only one valid C for each A and B, it's just a data column.

Three tables (A,B), (A,C), and (B,C) model a set of separate relationships. Looking up a row based on A and B only is possible and fully supported. What's more, normalisation rules say that these relationships should be independent - the existence of rows in (A,B) and (A,C) should not predict a row in (B,C), otherwise that row is duplicated data.

Other variations are possible, such as a table mapping A to B, with surrogate key AB, and a dependent table listing the relationship of those pairs to C (AB,C); or even a table mapping AB pairs to BC pairs.

There will be different scenarios where different things make sense. Can there ever be an A with a B and not a C? Would the same A always have the same B but different Cs? Does the relationship between B and C have a meaning outside the relationship of either of them to A?

From the additional info edited into the question, you might model the following relationships:

  • thing-A has feature-B, giving thing-feature-AB (foreign keys A,B; primary key AB)
  • feature-B has option-C, giving feature-option-BC (foreign keys B,C; primary key BC)
  • thing-feature-AB has feature-option-BC, giving price X (foreign keys AB, BC)

Note that here we have created additional entities, which might have their own information: I've listed the price as a likely output of the third table, but a thing-feature-AB might have restrictions on when that feature is available; a feature-option-BC might have additional description about how the option applies in that context; etc. These would be either columns, or further tables with foreign keys, such as thing-feature-restrictions with foreign key AB.

  • yes (A,B) and (A,C) do not predict a row in (B,C) – koalaok Oct 28 '16 at 8:34
  • also consider I want to achieve reusability of A and B, and C theirselves, that I can achieve with A-B, B-C, A-C model, and with A-B-C I see more redoundancy (i.e. B can be used by many A and many C) – koalaok Oct 28 '16 at 8:37
  • If (A=1,B=2) and (A=1,C=3) are valid, but (A=1,C=3) is not, then there is no way to represent that in an (A,B,C) table. It's three independent relationships, and the only design that makes sense is to model those three relationships separately. – IMSoP Oct 28 '16 at 8:41
  • I'm not sure what you mean by reusability, though. In all these tables, A, B and C should be nothing but foreign keys, representing the relationship only. – IMSoP Oct 28 '16 at 8:43
  • 1
    Q: "Which of these two (equivalent things) is better" A:"They are not equivalent, pick they one you mean" – Caleth Oct 28 '16 at 8:50
3

To be honest, your examples are a bit abstract to me. What I deduce however is that you have features B which can have options C. So what I would think is that the relations would be: you make a table D : B->C and and then have the relation D in your A table or a table : A->D

I imagine something like this: You have a car, which can have different rims, and each kind of rim is available in multiple colors, but not all rims can be in each color.

So what I would do is have my Car table, Rim Table and Color Table. Now make a cross table with the different rims and their color options and in you cars table you can make a reference in a field to a record of the cross table of rims and their colors so you get a specific rim with a color.

I you have more type of rims on your car you can make a cross table of cars and the rims-colors table.

0

If it really is a relationship formed of the triple, then you should have one table that is the triple, otherwise you are opening yourself to nonsense data when things are updated / deleted.

E.g.

Lets say you have A1, A2, B1, C1, C2, and the following:

A1, B1, C1        A1, B1 | A1, C1 | B1, C1
A2, B1, C2        A2, B1 | A2, C2 | B1, C2

What happens when you delete one of the A's, say A1? Now you have the following:

                         |        | B1, C1 <- Dangling relation?
A2, B1, C2        A2, B1 | A2, C2 | B1, C2
  • -1 for answering an unanswerable question. There's simply no way to know which is correct with the information given. – user82096 Oct 28 '16 at 8:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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