Re: Database Question

From: David Penney <anon_at_noaddress.com>
Date: Sat, 5 Jul 2003 20:32:01 +0000 (UTC)
Message-ID: <be7ck1$9ed$1_at_titan.btinternet.com>


Victor
is this the extent of complexity of your problem or only a simplified example with a much more complex problem in reality?

The other answers assume low complexity, I thought I would check. Is this a project for a product to be sold by your company or for a customer project? If its a simple problem, I'll help - if its a complex problem then my company can help.

Regards,

              David Penney
              www.metamatrix.com

"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 Sat Jul 05 2003 - 22:32:01 CEST

Original text of this message