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:
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!