Re: Database Question

Hi Victor

I would consider implementing common (frequently used) fields in main table (your option 1 below) and choosing option 4 for relatively "exotic" fields. This "combination" IMHO would be "fast and straight" in most cases, but still extendable.

--
Best regards,
Michael

"Victor Irzak" <victor_at_carmelvision.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ:
news:Wpsza.245045$w7k.112123_at_news04.bloor.is.net.cable.rogers.com...


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