Flexibility vs Performance question

Question

I'm in the middle of designing a database, and have a question for the veterans in regards to scalability.

Currently, I have table A, which represents some object, and table B, which contains key-value pairs that describe objects in table A.

If an object can have fields such as:

• type
• color
• taste
• isState
• etc

but doesn't necessarily have to have every field, is this an efficient model for scalability? a year from now, properties of objects in table A may change -- I may need to add more or remove some, and this seems more manageable than schema updates. However, it has the potential to create issues since all of my data is amalgamated into a single table. If table A has 20, 000 records, and each of those records links to 10 different things in table B, I have 200,000 records stored in table B.

Will this cause indexing issues as the number of records in table A grows? How does this compare in terms of a more traditional FKEY relationship design for things like a type and color table?

Any insight is most appreciated!

Answer 1

You can create a table for every property. That way you define the property with the data type that best fits each property. A similar question and answer that describes a simple example, complete with SqlFiddle, is here:

What's the best way to store and search through many product specifications

This might be a little iffy on the scalability side. Each new property means creating a new table, although that would have no effect on existing objects or the code that handles them. So it is expandable but I don't know the absolute limits. Queries are a bit on the complicated side, with at least one COALESCE and possibly a CASE statement or two, but not, I think, any more complicated than the EAV model.