I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable.
Say you are asked to record information on houses for a metro area, starting with a small neighborhood (200 houses) but eventually growing to 5000000+ houses.
You are required to store base information: ID# (A unique lot # we can use as a unique index), Addr, City, State, Zip. Fine, simple table will handle it.
But each year, you are going to be asked to record extra information about all of the houses - and WHAT information will change each year. So, for example, first year, you are asked to record the owners last name and square footage. The second year, you are asked to keep the last name, but dump the square footage and instead begin collecting the owners first names.
Lastly - each year the # of extra columns will change. Might start with 2 extra columns, then go to 6 next year, then back down to 2.
So one table approach is to try to add the custom information as columns in the house tables so there is only one table.
But I have a situation where someone laid out the tables for this as:
"House Table" columns: ID, Addr, City, State, Zip - with one row per house
ID Addr City State Zip
-------------------------------------------
1 10 Maple Street Boston MA 11203
2 144 South Street Chelmsford MA 11304
3 1 Main Avenue Lowell MA 11280
"Custom Info Table" columns: ID, Name, Value - with table looking like:
ID Name Value
1 Last Name Smith
2 Last Name Harrison
3 Last Name Markey
1 Square Footage 1200
2 Square Footage 1930
3 Square Footage
So there are multiple of rows for each individual house record. Each year when the optional information required changes, this table is literally rebuilt, so next year it might look like:
1 Last Name Smith
2 Last Name Harrison
3 Last Name Markey
1 First Name John
2 First Name Harry
3 First Name Jim
Eventually you amass 100,000 house rows AND one year there are 10 extra pieces of information; the second table now is 1,000,000 rows of information, many of which have redundant (description) information. The database requirements overall are that people will need to get the house row information + associated custom field values thousands of times per day.
So my question: Would it be bad (or horrible) practice to instead either:
A) Lay out house table with guess at max # of custom columns (called perhaps "1" through "10") and insert those custom values right in the house rows
OR
B) Store the custom information in the house table, but each year when the requirements change, rebuild the house table with only the # of columns needed for custom information, with the idea that the requirements could go nuts and you never know how many maximum optional fields might be asked for?
Thanks, hope this makes sense!