Table/Column Layout for multiple pricing

[JeremyReger]

Hello all,
I am wanting to structure a database that will allow for multiple prices per products. I also want to be able to upload an image and enter details about the product.

My initial thought would be to have a product_db and have a price table, a product table, and a description table all linked together with product id's

I am lost...

I would really appreciate your help on the table and column layout for achieving the mulitple pricing...

[pritaeas]

Can you give an example ? That may clarify what you want to achieve.

[yap]

Can you give an example ? That may clarify what you want to achieve.

create table product(proID integer not null, name varchar(100), primary key(proID));

create table price(picID integer not null, price decimal(10,2), primary key(picID));

create table product_price(proID integer not null, picID integer not null,
   primary key (proID, picID), 
   foreign key (proID) references product on delete cascade,
   foreign key (picID) references price on delete cascade);

This is a many-to-many relationship.

B U T: If prices depend on various deliverers, above solution is not optimal !

---
yap

[pritaeas]

Okay, why not create a table deliverers with their info and a link table deliverer_product_price.

[yap]

Okay, why not create a table deliverers with their info and a link table deliverer_product_price.

That smells of poor-people database design. I am afraid, dear pritaeas, you are fairly inexperienced wannabe db designer.

Would like to cite him: "Premature optimization is the root of all evil."
(you already know, it s Donald)

---
yap

[pritaeas]

Why would that be bad? Your deliverers and products have their own detail table and the link table contains the price. Do you really want a table that contains only an Id and a Price, so each price can exist only once ? That's not really functional, although that would be the fully normalized version. Unless there are some things you want to add to the price later, but you did not specify that.

Is a product delivered by 2 deliverers identical and only different in price ? Or are there more differences you did not mention yet.

Well, since you quote me, you know Donald was speaking of optimizing code (algorithms in particular), not DB designs.

But anyway, since you probably don't want advice from a wannabe db designer, I won't bother you again.

[yap]

Hi pritaeas,

Sorry, I have to apologize to you for my kind of impertinence. I really wouldn't hurt you. On the other hand, your suggestion of creating a further table deliverer what should then be linked either with product or price sounded to me in an amusing way.

If various deliverers be able to deliver various products all with arbitrary prices, one can create a linking table what implements the many-to-many relationship between product and deliverer. And price can easily be attatched to this linking table, for example:

create table product(proID integer not null, name varchar(100), 
   primary key(proID));

create table deliverer(delID integer not null, name varchar(50), 
   primary key(delID));

-- linking table contains prices now
create table product_deliverer(proID integer not null, delID integer not null, price decimal(10,2) not null, primary key (proID, delID), 
   foreign key (proID) references product on delete cascade,
   foreign key (delID) references deliverer on delete cascade);

Now a deliverer may be able to deliver quite a number products, and the same product might be delivered by various deliverers. Each purchase (identified by a pair of (proID, delID)) may have its own price. Sure, if price of such a pair changes in the course of time AND one wants to keep the price history primary key of table product_deliverer must be extended, for example by adding the delivery date (or better timestamp to handle multible deliveries per day), so the triple (proID, delID, date) would then identify each entry of this table. Ok to be saver, delete policy cascade might be changed into restrict.

sorry again.

---
yap

[pritaeas]

No need to apoligize. We were both just defending our own ideas, based on different views of the problem. You being the one with the most information. I must admit, if I knew after your question what I know now, I probably wouldn't have bothered replying. I'm a coder and really hate database design/normalization. The only reason I have to do it is because nobody does it for me (at my current job).

I'll let this be my last post in this thread, because I don't want to get in over my head ;D Good luck finding a suitable solution.