CODEWITHSUNDEEP
databasesqlalchemyrelational-databaserelationshipflask-sqlalchemy
carl
carl

Reputation: 4436

building complicated relationships in a relational database

I have a general question about relational database design. I have a large list of objects from a table. These objects are classified with three descriptor classes. There is distributor1, distributor2 and distributor3. To describe one object one always builds a triplet of these descriptors.

For example assume that descriptor1 is colour, descriptor2 is size and descriptor3 is weight. For each object I would build a triplet which describes that object. In my case I have thousands of entries for each descriptor. So I build a table for each descriptor. How can I now build a tripled and relate that to an object in the object table?

If there would be only one such tripled for each object, I could just store the three descriptor ids in each object as a foreign key, but let's assume that each object can have 0 or many such triplets.

I am using sqlalchemy, but I am happy to do the coding myself, I am just looking for keywords to look for in the documentation, since so far I could not find much.

My solution would be to create another table with three descriptor ids and and object id. Is that the way to go? I could also store a string with tripled of descriptor ids in each object... but that seems to go very much against the principle of relational databases...

Upvotes: 0

Views: 94

Answers (1)

user3112728
user3112728

Reputation: 405

There's rarely a perfect design for all scenarios. What you've described would work well, if you know that you'll never need another attribute and you'll always lookup that row by using all three attributes. It depends on your use case, but those are pretty limiting assumptions.

Adding more attributes or looking up records by 1 or 2 attributes instead of all 3 is when Lucas's suggestion of adding additional columns that can be indexed is more flexible. The ability to define an arbitrary set of columns within a non-clustered index is where the relational db tends to get a lot of it's search performance/flexibility.

Upvotes: 2

Related Questions