Database Question

Hi,

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 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.

I found 5 options to do that:
1) Adding these custom fields to the cars table. 2) Putting all the custom information in one additional field of the cars table and separating them by comma
3) Making a customCars table, putting all the custom fields there and linking it to the cars table, while maintaining 1-to-1 relation. 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.
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.

Option 1 fails because we are modifing the original table for every customer.
Option 2 fails, because the custom fields are not sortable and hardly searchable.
Option 3, 4 and 5 fail because they double and triple the amount of tables, which makes it rather complicated.
Option 3 also modifies custom tables for each customer.

Are there any other options that you can see? If not, which option do you find most suitable?

TIA, Victor Irzak Received on Fri May 23 2003 - 18:57:26 CEST