CODEWITHSUNDEEP
database-design
Michael
Michael

Reputation: 42050

Naive question about storing some entity attributes in the database

I am designing a database-centric web application. I have noticed that some entities have attributes, which are not used in selecting, sorting, and grouping. They are just plain and simple data holders, stored in the database and updated by GUI, e. g. attribute Middle Name in entity User.

So, I am planning to store ALL these attributes together in a varchar field as a string (in JSON format). I think it makes the data access layer simpler since I do not need all that JSON to/from SQL conversion for each entity. Additional benefit is to add/remove these attributes w/o altering the database schema. Does it make sense ?

Upvotes: 1

Views: 230

Answers (1)

jon_darkstar
jon_darkstar

Reputation: 16768

I don't think the single varchar is a good idea. I guess you plan to append new ones at the end? It would make supdating/deleting very difficult.

EDIT - (thanks nox!) - Your situation is not unique, its a common strategy called EAV (entity-attribute-value) and I use it myself along with many others. http://en.wikipedia.org/wiki/Entity-attribute-value_model Implementations of it vary, and I've suggested one below that I hope can work for you.

Try the following table structure:

entity
id, name, .....

attribute - example (3, 'Middle Name')
id, name, [content_type], [choice_type], [parent]

attribute_value - example (1, 3, 'Xavier')
id, attribute_id, value

entity_to_attribute_value
id, entity_id, attribute_value_id

To get X's attributes:

SELECT *
FROM attribute A 
   LEFT JOIN attribute_value AV on AV.attribute_id = A.id
   INNER JOIN entity_to_attribute_value ETAV on ETAV.attribute_value_id = AV.id
WHERE ETAV.entity_id = X

I listed a few optional fields on attribute for the following

content_type:
This could be done as an enum or foreign keys to another table. Can indicate numeric, positive numeric, string, ex. Useful for validating input since value is a varchar and will allowing anything

choice_type:
Can indicate whether user enters whatever they want (create new attribute_value), chooses only one from those you've already set up, or chooses one-or-more that you've already set up. On a form entry page this will dictate the kind of form element (input, select, select w/ multiple)

parent:
Points to another attribute for hierarchical relationships between them like country and state, for example. This will potentially affect both the display and logic on the form page.

When generating the GUI form you'll want to make elements for all attributes, and adding new ones is as simple as a row in the table.

Upvotes: 2

Related Questions