Re: Database Question

Hi, I got 3 options for you

If you want a simple solution you could just add one column called 'Comments' and tell your users to put all extra info in there

Another good solution would be adding those color, weight, fuel tank capacity columns and give them to all users and allow them to leave them empty in your user interface. Probably the customer that doesn't want a 'color' column now will want it later... Adding those options to your system will make it better.

A more complex solution would be to create a table PropertyNames that the users can add car characteristics to like 'color' and whatever they want. Then make a table PropertyValues containing a reference to one row in PropertyNames, a reference to a car, and a column for entering string data in. The only difficulty is showing an overview of cars with one line for each car. You'll need a crosstab for that ;)

Bas

"Victor Irzak" <victor_at_carmelvision.com> wrote in message 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.
>
> 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 Sun May 25 2003 - 18:34:25 CEST