Simple one for a DBA

Question

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

Vulpes · 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?

Vulpes · Answer

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 User · Answer

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 User · Answer

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

Vulpes · Answer

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.