Re: Database Question

From: Parker Shannon <pshannon_at_ixpres.com>
Date: Wed, 9 Jul 2003 08:33:38 -0400
Message-ID: <3f0c0770_1_at_news.vic.com>


Sounds like a Car can have many Customers and Customer can have many Cars. The Relation then is CustomerCar.

The killer in your app is having the CustomerCar attributes searchable and sortable. I would need to know the purpose of searching and sorting to optimize these fields for query. What I mean by that is: (1) are the values of these fields searched and sorted while somebody is waiting at a workstation for an answer or (2) are the values of these fields searched and sorted by a query or a report running in batch mode?

Entry is key. If Entry is on Car or Customer and you are searching by Car (Name.Model.Year) or by Customer (Name) you can reach the data in CustomerCar very quickly with the model below. This query is interractive.

If Entry is on CustomerCar, i. e.:

SELECT Car.Name, Customer.Name, CustomerCar.Color FROM Car, Customer, CustomerCar
WHERE CustomerCar.Color = "Yellow"

This query is a batch query, returns all the "Yellow cars" and suitable for reports run in a low priority region or a very patient user at a workstation. If CustomerCar.Color is indexed the query will execute much faster because it will only return the rows from CustomerCar containing "Yellow". If CustomerCar.Color is not indexed, the query must examine every single row in the CustomerCar table for the Color = "Yellow".

Once again, the design answer to your problem is where does the query Enter (Entry), on Customer, Car or CustomerCar.

Car (table)
Id - primary key - unique - long integer - indexed Name - secondary key - not unique - secondary key element - indexed Model - secondary key - not unique - secondary key element - indexed Year - secondary key - not unique - secondary key element - indexed attributes list . . .

Customer (table)
Id - primary key - unique - long integer - indexed Name - secondary key - unique - indexed ( example: "Jones, Joseph J." or "Jones, Joseph J., Albany, NY" when dupes) attributes list . . .

CustomerCar (table)
CarId - primary key - not unique - primary key element - indexed CustomerId - primary key - not unique - primary key element - indexed Color - may or may not be indexed
Weight - may or may not be indexed
NumberCylnders - may or may not be indexed TireSize - may or may not be indexed
Etc.

"David Penney" <anon_at_noaddress.com> wrote in message news: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 Wed Jul 09 2003 - 14:33:38 CEST

Original text of this message