CODEWITHSUNDEEP
databaserelational-databasedatabase-schema
agusgambina
agusgambina

Reputation: 6699

Best practice for modeling a recursive relation in a table in a relational database

I am modeling the tables for an schema. I have the following doubt, which would be the best practice to model this.

I have a product (table product), a product can contain several products, for example, a product car, can have four tires. On the other hand a product also can be related with several layers of products, for example, a car can have a board which may have several lights. In the last example there exists three levels, but the number of levels is unknown.

My first question is, which would be the best practice to model this?

Is there a way to don't have a looping relation? I mean, if the product A has the product B, and the product B, has the product A, then there is a loop

thank you in advance

Upvotes: 1

Views: 205

Answers (1)

leeor
leeor

Reputation: 17801

Self-referential tables can work fine for simple cases, but as you suggest, can get nasty (and perform poorly) if there are too many levels.

One consideration is that you may be over-generalizing your problem domain. Does it really make sense to treat cars and their components in the same way? Is the relationship between parts relevant to the problem you are trying to solve? Is a loop scenario even possible in practice?

It's hard to suggest a schema without knowing more about the problem you are trying to solve and the constraints.

Upvotes: 1

Related Questions