Simple one for a DBA

Guest User

Hi

I am designing and implementing an E-Commerce solution and needed a pointer on table constraints

I have the following tables

Category

CategoryId PK
CategoryName
Description
Version

CustomerEntity

CustomerId PK
CustomerName
City
Country
Version

Product

ProductId
CategoryId
ProductName
UnitPrice
UnitsInStock
Version

Order

OrderId
CustomerId
OrderDate
Version

OrderDetails

OrderId
ProductId
UnitPrice
Quantity
Version

Given the above I would like to implement a shopping cart table but do not know what columns to have nor what tables to reference with a Foreign Key?

Thanks

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Accepted answer
Well, your proposed Cart table is very similar to your OrderDetails table.

In fact if you add a CartId column to the latter, it's virtually the same because the ProductName column could be pulled in from the Products table using the ProductId.

Also, the OrderDetails table is duplicating the Unit Price column (and possibly Version) which it could pull in from the Products table using the ProductId, unless there's a possibility that the Unit Price could be discounted for some reason.

So, I'm wondering whether you really need another table?
Vulpes14y ago
As a general principle when designing your database schema, it's a good idea to eliminate as much duplication of columns as possible by use of foreign keys.

When an update is needed, you only then need to update it in one place which reduces the risk of the database becoming inconsistent.
Guest User14y ago
Well said Vulpes and many thanks. I was thinking and looking too deeply into the situation that I lost sight of what was already there!

Cheers again.

Steven
Guest User14y ago
Thanks Vulpes.

I should of been more clearer in my post.

I have allocated the Foreign Keys already on the existing tables.

My question was about how to add a Cart table and what columns should I implement. Im thinking CartId, Quantity, UnitPrice, ProductName, ProductId, Order Id.

Given these columns I will implement OrderId as the Primary Key, should I then reference the OrderId in the Order table to get the latest number for the order?

Thanks
Steven
Vulpes14y ago
Well, foreign keys point to primary keys in other tables.

So, I'd say that as it stands:

1. The CategoryId column of the Product table should be a foreign key which points to the CategoryId primary key column of the Category table.

2. The CustomerId column of the Order table should be a foreign key which points to the CustomerId primary key column of the CustomerEntity table.

You might also want to consider making OrderId a primary key of the Order table and ProductId a primary key of the Product table so you can create foreign keys in the OrderDetails table which point to them.