0

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!

  • 1
    Just FYI, the scheme you are proposing is referred to as "Entity-Attribute-Value", or EAV for short, and you may want to search that up to see the large number of articles talking about performance of that model. – Max Vernon Sep 30 '15 at 16:52
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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