1

I'm pretty new to data modeling, so please excuse me if I've missed something obvious. I'm trying to decide on a schema (or possibly a DBMS) to work with some data where there are quite a few possible attributes (60-100 maybe), but only a handful will apply to each record. Here are my options as I see them.

Option 1: Very wide table

Just include each of the attributes as columns and most will be NULL for any given record.

Pros: keeps schema simple, keeps queries simple

Cons: inelegant, unwieldy, adding/removing properties requires a schema change

Option 2: Entity-attribute-value model

Separate table(s) for listing attributes and their values. This is how I've seen this problem handled before. For example, WordPress uses something like this for their *meta tables.

Pros: easy to understand conceptually, flexible

Cons: query construction becomes a pain, performance suffers, you wind up with a lot of duplicated data (i.e. the same property name is listed over and over).

Option 3: NoSQL Document or Graph DBMS

Go with a DBMS that's built to handle schema-less data. I've looked at MongoDB, RethinkDB, and OrientDB. OrientDB looks really cool, but I don't know Java, and it seems like it's primarily a Java thing (the PHP driver, for example, looks problematic).

Pros: built to handle flexible schemata, fast

Cons: relationships seem difficult, I have no experience with them, seems like a lot of duplicate data (e.g. what happens if I want to change the name of a property?), querying seems way more complicated

As a side note, this application will not get very much traffic, and will have very few concurrent connections. So, scalability is a fairly minor concern. Thanks for any suggestions.

closed as primarily opinion-based by Mark Storey-Smith, Max Vernon, RolandoMySQLDBA, Aaron Bertrand Jul 14 '14 at 20:23

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    If "scalability is a fairly minor concern" what are you trying to optimize? For a low-traffic database it will not be of any major importance which of the options you choose. In that case, go with the option you understand best, and can easily support in future. – Max Vernon Jul 11 '14 at 13:22
  • I don't understand why option 1 has to be a very wide table as opposed to a series of small related tables i.e. what is wrong with a traditional relational database design? – datagod Jul 11 '14 at 13:32
  • MaxVernon, good question. I've seen performance issues (like in the question I linked) even with very low traffic when the data design has issues. datagod, maybe I'm just not seeing it, but if I broke it up into multiple tables, wouldn't I be looking at something more like Option 2? – Dominic P Jul 12 '14 at 1:59
  • Option 1 is convenient when you want to use data that pertains to all rows, regardless of subclass. The width of the table adds unnecessary bulk, slowing things down. But it's easy. See my Option 4 answer for an alternative. – Walter Mitty Jul 13 '14 at 10:53
5

Option 4: Class Table Inheritance

This is a design technique used to implement a class/subclass situation. In this situation, attributes often apply only to one subclass, and not to all rows in the table. Visit the tag of the same name over in SO to see a bunch of relevant questions and answers.

This has the advantage eliminating NULLS (mostly), providing fast and easy joins, and enforcing the 1:1 neture of the class:subclass relationship.

Your Option 1 is sometimes called "Single Table Inheritance"

  • I hadn't heard of this technique before. It sounds really interesting. I'll do some research, but do you know if this type of thing that can be implemented in a MySQL database? – Dominic P Jul 12 '14 at 1:58
  • I haven't built a MySQL database, but I can't see where the technique would require anything that MySQL doesn't offer. It's just a collection of Tables, rows, columns, primary keys, and foreign keys. – Walter Mitty Jul 12 '14 at 12:04

Not the answer you're looking for? Browse other questions tagged or ask your own question.