CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
grimurd
grimurd

Reputation: 2850

How to store custom entity properties in a relational database

I'm designing a database for a application I'm developing.

I have two tables where the entities can have custom application properties that will define how the data from each entity is used.

Each entity can have zero to many properties and i want to know what the best way is to structure such data in a relational way. Should i have one table with foreign keys to each table and key/value columns or is there some better way to do it?

Upvotes: 1

Views: 1074

Answers (1)

KingOfAllTrades
KingOfAllTrades

Reputation: 421

You are speaking of "Entity Attribute Value" tables.

Entity Tables:

Ent1 (Eid, name, desc, etc...)
Ent2 (Eid, name, desc, etc...)

Attribute (EAV) Tables:

Ent1Props (id, Eid, att, value)
Ent2Props (id, Eid, att, value)

You can combine the two EAV tables into one and that creates a MUCK (Massive Unified Code Key) table. But don't do that as it has some real drawbacks in the long run. Nearly everyone has tried it at one point because it seems easy at first, but it becomes a monster.

Key value pairs in relational database

Upvotes: 2

Related Questions