CODEWITHSUNDEEP
mysqlsqldatabase
Markus Knappen Johansson
Markus Knappen Johansson

Reputation: 1620

Best way to model a database with a object that has custom properties with values

I'm working on a system where I need to create an "root object" that should be able to have zero or more configurable properties.

Let's say that its a customer, we always know that we want to store the customerId, name and birthdate - this would be columns in a table. But the system should be able to handle custom properties for the customer.

Let's say the the user of the system also want's to store city, phone and email for this customer. How would I model the database in the best way to support this?

This is what I'm thinking in terms of tables:

Customer
 - CustomerId
 - Name
 - Birthdate

CustomerProperties
 - CustomerPropertyId
 - Name

CustomerPropertyValues
 - Id
 - CustomerId
 - CustomerPropertyId
 - Value

With this set up the "City" would be a CustomerProperty in the "CustomerProperties"-table and the value for that property would be stored as a entry in the CustomerPropertyValues-table.

This way I would be able to query for all customers in city X using SQL.

I've been thinking about storing all the custom properties as a json blob but that would make the querying quite hard and i guess that this would not perform good.

What I'm wondering is: Is this my example of db-modol a good approach? Is there any better way to model this? Anything that I should "be aware of" in terms of performance, challenges etc. around this?

Thanks for any help or feedback!

Upvotes: 3

Views: 972

Answers (1)

sanepete
sanepete

Reputation: 1120

Having had to do something similar on numerous occasions my own approach for ease of use, maintainability and robustness would be:

Customer
 - CustomerId
 - Name
 - Birthdate

CustomerProperties
 - CustomerPropertyId
 - CustomerId
 - Name
 - DataType
 - Value

Depending on the number of records you expect to keep and the different types of data, you may wish to normalise it by adding a separate table for each datatype:

Customer
 - CustomerId
 - Name
 - Birthdate

CustomerDateTimeProperties
 - CustomerPropertyId
 - CustomerId
 - Name
 - Value

CustomerIntegerProperties
 - CustomerPropertyId
 - CustomerId
 - Name
 - Value

CustomerStringProperties
 - CustomerPropertyId
 - CustomerId
 - Name
 - Value

Then the Value field datatype can be set and you avoid all the type casting. These are the approaches that have worked best for me in the past, I hope this helps.

To get all properties for a customer:

SELECT * FROM Customer, CustomerDateTimeProperties, CustomerIntegerProperties
INNER JOIN CustomerDateTimeProperties ON CustomerDateTimeProperties.CustomerId = Customer.CustomerId
INNER JOIN CustomerIntegerProperties ON CustomerIntegerProperties .CustomerId = Customer.CustomerId

INNER JOIN etc...

WHERE Customer.CustomerId = @CustomerId

If you're using my first example just use one join to the CustomerProperties table.

Upvotes: 4

Related Questions