How to handle table design with variable columns

Question

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!

Answer 1

You have pretty much 4 choices:

NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB.

EAV - definition This is where you turn either the whole table or a portion (in another table) on its side. This is a good choice if you already have a relational database in-house that you can't move away from easily. The custom info table example you gave is a good example of an EAV table.

Standard tables with XML columns - Think of this as NoSQL meets relational tables. The data stored in an XML column can be any format that XML supports, including multiple correlated sub-data. For the columns that you know are going to be "regular" columns, they can be built as the appropriate type of column to store the data (LastName, Address, City, State, etc.).

Standard tables with lots of extra columns - You have a relational database, you can't use either XML or EAV, and NoSQL is not an option. Add lots of extra columns of each type. I would guess 30 or more varchar, 30 or more integer, 15 or more numerics. And once you use a column for a value, don't re-use it. And don't delete the column either.

Out of all of these solutions, my own opinion is that you will find either the NoSQL or the EAV approach to be the most successful with the least amount of refactoring your code and your schema.

You will have a situation where you collect data one year, not the next, and then collect it again afterward. Trying to get the older data updated with the correct information is problematic and expensive. Storage is neither.

Answer 2

To answer your question on those 2 options, neither seem right to me. A) will lock you in and B) is a lot of work. The current schema you describe is not too bad (except for having the information name ("first name", "square foot", etc) as string instead of an ID referenced to a lookup table.

However, this seems to me like a good candidate for a NoSQL database ( http://en.wikipedia.org/wiki/NoSQL ). While I never worked with such database, what you describe is a typical scenario that this solves.

Answer 3

If the concurrent number of custom columns is finite and the limits are known ( e.g. no more than 10-20 Custom columns for String's, no more than x columns for integers etc.)
You could use the base table with extra fields per datatype and instead of rebuilding the table every year create a view for that year including only the relevant custom columns and renaming the generic fields to reflect the contents for that year.

House Table:
ID, Addr, City, State, Zip, custom_string1,cs_2,cs_3,custom_integer_1,ci_2,ci_3 ...

create view house_2014 as 
select ID, Addr, City, State, Zip,
custom_string1 as last_name,cs_2 as first_name ...

The problem with this approach is, that you have no history but you could easily make a copy every year before changing the column requierements.

create table house_2014_archive as select * from house_2014;
drop house_2014;
create view house_2015 as "select column list for new year";

Answer 4

Can you enumerate all the scenarios for which you would like to store this data?

if there is a finite number of columns combinations that may be applied to the table, then try to model a "base table" with common columns that are gpoing to apply to all scenarios, then create more tables (to implement some kind of inheritance; this is known as subtype / supertype in ERD and database design.)

one table for each scenario, this way at least you will keep the tables clean and you will be able to avoid having street address stored in "last name" column...

take a look at this design question : https://stackoverflow.com/questions/554522/something-like-inheritance-in-database-design