[RESOLVED] Database Design Question

SundariDevi

I have a database of products and I'm trying to decide the best way to set up the database.

There are about ten products, each of which have some or all of a list of 20 features. While a few products have all 20 features, most only have 5-10. So what is the optimal way to set up the products tables?

Initially I had a products table with a product ID and a list of all the features (some features are yes/no, some have other properties

But then since the least feature complete product only has 5 features, I thought, the database has a lot of empty space. So I was thinking about setting up a table for each product. But then I would need a lookup table to get the table name from the product id. On the other hand, ten products is not a lot, so there won't be much empty space.

So is the best way to just do one products table? When would it be better to have a table for each product?

bradgrafelman

Can you show us your current database schema and perhaps some sample data that shows some products with different features? Also, what do you mean by "some [features] have other properties" - can you give us examples of those, too?

It sounds like you're doing a one-to-many relationship between products and features, so I would have three tables:

products
features
product_feature_xref

Rows in products and features should have some primary key (such as an AUTO_INCREMENT column). The product_feature_xref table, then, would simply be a table relating those two keys.

So, if product ID #123 had features 1, 2, and 4, you'd see three tuples in the product_feature_xref table: (123,1), (123,2), and (123, 4).

SundariDevi

My schema is in an excel spreadsheet, and my example is just an excerpt because I'm trying to simplify it just to illustrate the part in the question.

But here's more info. It's a booking engine for private or shared car hire. So the cars are different products. Also there are different prices for different options and different trips. Actually nothing is yes/no, they are mostly prices

Here is a sample Car (product) table. Some cars could have 20 prices

vehicleid int(3) zerofill unsigned not null primary key
Name char(30) notnull
capacity tinyint unsigned notnull
pricea1 smallint unsigned
pricea2 smallint unsigned
pricea4 smallint unsigned
priceby1 smallint unsigned
priceb2 smallint unsigned
priceb4 smallint unsigned
child seat tinyint unsigned
luggage tinyint unsigned
partnerid int(3), unsigned not null

partner table
partnerid int(3), unsigned not null primary key zerofill
name char(30)
basemethod enum('baserate', 'percent')
commission1 float 4,2 unsigned
commission2 float 4,2 unsigned
commission3 float 4,2 unsigned
commission4 float 4,2 unsigned

bradgrafelman

Any time I see a group of numbered columns (e.g. a single column/attribute is being repeated within the same table), that immediately sends up red flags.

Although I still don't understand what all of the price fields contain, it is sounding like my suggestion above is becoming more and more relevant. Can you show some of the sample data I requested above?

SundariDevi

numbered column is just a short hand for price for a certain trip.

It could be like

Price1 (e.g. LAX - Santa monica)
Price2 (e.g. LAX - Malibu)
Childseat = $xx

So you see, number is irrelevant here

You should be able to assume everything is an ID (partner or vehicle) a price, or an integer (like capacity)

bradgrafelman

So what happens if you add or delete a trip? You have to change the entire schema?

SundariDevi

Why can't i just add a new column to the Car table?
It wouldn't be good to delete columns (trips) because bookings would already have been made using them

The commissions are either a percentage or a cost-plus. So if a new commission rate is negotiated, say holiday commission rate. Then I just add a new column to the table.

jeepin81

since it is based on items #'s why not have two tables:

itemmast
itemfeat

set the part no's as the key and left join on the part no.?

select iitem, idesc, ikeywds, fitem
left join files.itemfeat on iitem = fitem
from files.itemmast
where iitem = 'SKU123'

SundariDevi

Sorry my original post was not accurate. The product - feature analogy doesn't work.

There are different products (cars) with a variety of different prices depending on what kind of trips they make. Not all cars have all prices (because not all cars can do all trips) and different cars have their own unique price fo the same trip, so it's not YES/NO.

So the idea is when a booking is made, the price and some other attributes (like capacity) need to be fetched from the Product table.

So do i make one big table with all the cars and have columns for about 20 different prices, even though for many cars most prices don't exist, or do i do it a different way?

bradgrafelman

SundariDevi;10993665 wrote:
So do i make one big table with all the cars and have columns for about 20 different prices, even though for many cars most prices don't exist, or do i do it a different way?

I would again suggest the same three table model I proposed above.

SundariDevi

I would again suggest the same three table model I proposed above.

If I understand your suggestion correctly, that won't work because Feature is a variable not a constant. It is a Price or a quantity.

So, if product ID #123 had features 1, 2, and 4, you'd see three tuples in the product_feature_xref table: (123,1), (123,2), and (123, 4).

I would have

features (trips)
1: LAX-MALIBU
2. LAX-STAMONICA
3: LAX-NEWPORT

xref table
carid 123: LAX-MALIBU
carid 123: LAX-NEWPORT

With this model, I know what trips it can do, but I still won't know the price

bradgrafelman

Right, but the price is an attribute of the two-way join between trips and cars, so you could add the price column to the xref table.

SundariDevi

OK, but only something like 2 or 3 cars have the same price for any given trip, so there is little gain by not making a separate table for each car. At least I don't have to do any join that way, so it will be faster.

bradgrafelman

SundariDevi;10993684 wrote:
OK, but only something like 2 or 3 cars have the same price for any given trip, so there is little gain by not making a separate table for each car.

Who said anything about making a separate table for each car??? That would certainly be ridiculous.

I called my suggestion the "three table model" for a reason - there's only three tables needed.

SundariDevi;10993684 wrote:
At least I don't have to do any join that way, so it will be faster.

Not necessarily, and not doing it that way could easily lead to a schema that a) isn't normalized, b) is harder to understand, and c) is harder to modify.

SundariDevi

Right now I have two possible designs

table with a row for each car and a column with a price for each trip
table for each car with columns for the trips it can do

In (1) I have a lot of empty space.
In (2) I have a lot of tables.

In your 3 table design, I will have a product table with something like 60 or 70 prices in it.

Either 1 or 2 will be faster and I don't see any normalization issues

bradgrafelman

SundariDevi;10993687 wrote:
In your 3 table design, I will have a product table with something like 60 or 70 prices in it.

No, you'd have a product table with zero prices in it; the prices would be in the 'xref' table where a car is linked with a trip at an associated price, e.g. the xref tuples would now look like: (123, 1, 50.00), (123, 2, 75.00), etc. where '50.00' and '75.00' are prices.

SundariDevi;10993687 wrote:
Either 1 or 2 will be faster

Oh? You're fast - it would've taken me much longer to build each proposed solution, perform several benchmarks, and compare the resulting data. Nicely done!

Do the possible trips never change? If so, then perhaps option #1 is indeed good enough. I can't see any scenario where option #2 above makes sense, though.

The benefit with my 3 table setup is that you can add/delete cars and/or trips with zero schema changes; the same can't be said for the two options you've described above.

SundariDevi

Yes xref, not product table

Oh? You're fast - it would've taken me much longer to build each proposed solution, perform several benchmarks, and compare the resulting data. Nicely done!

I assume that querying a specific table is faster than joining three tables some of which have many more rows.

But agree, 2 is bad and one is not optimal. That's why i'm posting here in the Newbie forum.

I think everybody is assuming that trips look like this

LAX - MALIBU = $100
LAX - SantaMonica = $50

But in fact it doesn't look like that. It's more like:

Mercedes S-Class:
LAX - MALIBU = $250
LAX - SantaMonica = $150

Minibus
LAX - MALIBU = $200
LAX - SantaMonica = $100

Audi or BMW
LAX - MALIBU = $150
LAX LAX - SantaMonica = $80

Shared Van - 1 passenger
LAX - MALIBU = $50/person
MAX - SantaMonica = $30/person

Shared Van - 2-3 passenger
LAX - MALIBU = $40/person
LAX - SantaMonica = $25/person

Shared Van - 4+ passenger
LAX - MALIBU = $30/person
LAX - SantaMonica = $20/person

So the xref table would end up with more than 70 columns (that is a quick guess -- it could be many more)

bradgrafelman

SundariDevi;10993694 wrote:
So the xref table would end up with more than 70 columns (that is a quick guess -- it could be many more)

No, the xref table would have three columns: car_id, trip_id, price.

Quick example:

[code]cars (car_id, car_name)

(1, 'Mercedes S-Class')
(2, 'Shared Van')

trips (trip_id, trip_name)

(1, 'LAX - MALIBU')
(2, 'LAX - SantaMonica')
(3, 'MAX - SantaMonica')

car_trip_xref (car_id, trip_id, price)

(1, 1, 250)
(1, 2, 150)
(2, 1, 50)
(2, 3, 30)[/code]

SundariDevi

No, the xref table would have three columns: car_id, trip_id, price.

Quick example:

Code:

cars (car_id, car_name)

(1, 'Mercedes S-Class')
(2, 'Shared Van')

trips (trip_id, trip_name)

(1, 'LAX - MALIBU')
(2, 'LAX - SantaMonica')
(3, 'LAX - SantaMonica')

car_trip_xref (car_id, trip_id, price)

(1, 1, 250)
(1, 2, 150)
(2, 1, 50)
(2, 3, 30)

Sorry , I meant the xref table will have 70+ rows. For example, the car table is correct, but the trips table would have to look like this:

trips (trip_id, trip_name)

(1, 'LAX - MALIBU/private'')
(2, 'LAX - MALIBU/1pax')
(3, 'LAX - MALIBU/2-3pax')
(4, 'LAX - MALIBU/4+pax')
(5, 'LAX - SantaMonica/private')
(6, 'LAX - SantaMonica/1pax')
(7, 'LAX - SantaMonica/2-3pax')
(8, 'LAX - SantaMonica/4+pax')

So if there are say an average of 8 possible trips and ten cars, then we have 80 rows (not columns -- ugh!)

Anyway, even if i do it that way, why is it easier to update this schema? If I add a LAX - San Diego trip, I have to update the trip table (add maybe 4 or more rows) as well as the xref table. If I add a car I have to add a row to the car table and 4 or more rows to the xref table.

If I use my Option (1) above, if i add a trip, i just add up to four columns to the car table. If I add a car I just add one row.

Is adding columns worse than adding rows?

Thanks for your help

Weedpacket

SundariDevi wrote:
Is adding columns worse than adding rows?

Definitely.

Think of index cards. The columns are the lines that appear on each index card, and each card contains the information in one row. The table is a collection of index cards. It's easier to get more index cards than it is to rewrite all the cards you have to make room for more lines (which might mean copying everything to bigger index cards when you run out of space).