Re: Database Question

Victor Irzak (victor_at_carmelvision.com) writes:
> I'm working on a product that stores information in the database. For
> example I have a table called cars, which includes fields like:
> model, year, serial number.
>
> Different customers would like different custom fields and we have to
> provide them with these fields. For example, one customer would like
> colour as an extra field, and another would like weight and fuel tank
> capacity fields.

I suspect that your actual problem is more complicated. This makes your question difficult, because my preference for a solution would probably depend a lot on the actual case.  

> I have to allow to store that information in the database. The
> requirements are these:
> 1) Make the custom fields searchable and sortable.
> 2) Don't change the structure of the database for each customer.
> 3) Don't make the structure too complicated.

Hehe. We all want that silver rainbow, don't we?

Well, there is one option that you did not list: include all columns in the cars table at all customer sites, and then have a parameter table which informs the GUI, which columns that are actually available. Or simply give the customers some extra columns for free.

I have no idea whether this is an acceptable solution to you. I suspect that it is not, but satisfies your requirements above.  

> I found 5 options to do that:
> 1) Adding these custom fields to the cars table.

By site. This is a possible solution, if you have some good means to control this. In our shop this solution is not completely out of the question, because our toolset includes a pre-processor, and in each database we store which macros that apply to that database. However, today we have no such tables, and I would think twice before I took that road. (Usually I just dump in the extra column, as the users only are supposed to access the database through our application.

> 2) Putting all the custom information in one additional field of the cars
> table and separating them by comma

Out of the question.

> 3) Making a customCars table, putting all the custom fields there and
> linking it to the cars table, while maintaining 1-to-1 relation.

This is an alternative to my suggestion to include all columns. Rather than having them in the main table, you have them in a side table. Hm... I don't think I see enough benefit in this.

> 4) Making a customCarFields table with id and customCarFieldName fields,
> putting all the custom car fields as entries in the table and having a
> junction table to connect between cars and customCarFields tables and
> provide the value.

We have a few tables like in this in our system. While complex, it saves us from adding extra columns for every nitty-gritty requirements that comes up. One bad part here, is that a misspelling in field name is not going get going by the DB engine. Search is probably going to be difficult too.

But the amount of complexity is still manageable in my opinion, and if I am not permitted to include all columns, I would probably take this road.

> 5) Making a table called customFields with id, customFieldName and
> customFieldType. This table will contain custom fields for all tables:
> cars, trucks, airplanes, etc. There will be many junction tables
> connecting these tables to the customFields table.

So you have more than one table where you have this problem? While this would give you fewer tables than 4, it will be even more complex. I would stay out of this one.

-- 
Erland Sommarskog, SQL Server MVP, sommar_at_algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp