How to store data with dynamic number of attributes in a database

by

If you ever plan on searching for specific attributes, it’s a bad idea to serialize them into a single column, since you’ll have to use per-row functions to get the information out – this rarely scales well.

I would opt for your second choice. Have a list of attributes in an attribute table, the objects in their own table, and a many-to-many relationship table called object attributes.

For example:

objects:
    object_id    integer
    object_name  varchar(20)
    primary key  (object_id)
attributes:
    attr_id      integer
    attr_name    varchar(20)
    primary key  (attr_id)
object_attributes:
    object_id    integer  references (objects.object_id)
    attr_id      integer  references (attributes.attr_id)
    oa_value     varchar(20)
    primary key (object_id,attr_id)

Your concern about performance is noted but, in my experience, it’s always more costly to split a column than to combine multiple columns. If it turns out that there are performance problems, it’s perfectly acceptable to break 3NF for performance reasons.

In that case I would store it the same way but also have a column with the raw serialized data. Provided you use insert/update triggers to keep the columnar and combined data in sync, you won’t have any problems. But you shouldn’t worry about that until an actual problem surfaces.

By using those triggers, you minimize the work required to only when the data changes. By trying to extract sub-column information, you do unnecessary work on every select.

More Related Contents:

  1. Should I normalize my DB or not?
  2. Reorder / reset auto increment primary key
  3. MySQL Trigger after update only if row has changed
  4. MySQL Query to select data from last week?
  5. Select last N rows from MySQL
  6. How can I modify the size of column in a MySQL table?
  7. MySQL InnoDB foreign key between different databases
  8. How to insert data to MySQL with auto-incremented column(field)?
  9. How to design a multi tenant mysql database
  10. SQL Order By Count
  11. Replace Into Query Syntax
  12. Implementing a hierarchical data structure in a database
  13. What resources exist for Database performance-tuning? [closed]
  14. How to connect to MySQL from the command line
  15. Remote connect to clearDB heroku database
  16. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  17. Laravel join with 3 Tables
  18. MySQL foreign key to allow NULL?
  19. How to schedule a stored procedure in MySQL
  20. What’s the best way to migrate a Django DB from SQLite to MySQL?
  21. How to get multiple records against one record based on relation?
  22. What are the differences between the BLOB and TEXT datatypes in MySQL?
  23. What is the best way to store media files on a database?
  24. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  25. Foreign key constraints: When to use ON UPDATE and ON DELETE
  26. How to use a user variables in MySQL LIKE clause?
  27. remove all numeric characters from column mysql
  28. MySQL – Complexity of: SELECT COUNT(*) FROM MyTable;
  29. Inventory management with stock options
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment