We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:
1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)
2) We have a Tables called : Interest & Customer_Interest
Customer Table:
CustomerID, Customer Name, Address, Email, Signup Date
Interest Table:
InterestID, InterestName
Customer_Interest: (Need suggestion for How to design this)
Should Table be design like:
Option1: CustomerID, InterestID
Option2: CustomerID, Interest1, Interest2, Interest3, Interest4
i.e.
Lets Say:
Customer table has CustomerA, CustomerB, CustomerC
Interest table has Interest I1, I2, I3, I4
Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4
As per Option1:
Customer_Interest Table witll have
CustomerA, I1
CustomerA, I2
CustomerA, I3
CustomerB, I1
CustomerB, I4
OR
As per Option2
Customer_Interest (Where Interest Column is bit column.... 1 = Signed up, 0 = Not Signed up
CustomerA, 1, 1, 1, 0
CustomerB, 1, 0, 0, 1
Which way we should design?
3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?
Thanks !!!You should go with Option 1. It would scale as the interest table changes. Option 2 would be a maintenance nightmare to change as the Interest table changes - not to mention, you would have to interpret the boolean values.|||1) What are the business requirements for identifying a customer? Does the business provide a Customer Number? If the requirements provide a unique identifier, then use what the business provides.
2) As the other poster said, your second option wouldbe a total nightmare. If you really wanted data presented in that view, then you can easily create a view to do that from your normalized table.|||
1) We are going to assign CustomerID. Usually we use Identity Column as CustomerID. After reading these articles, SQL server Forum, this forum etc etc, we started thinking if we should use Identity column as CustomerID or in our system people has to login.. so Can we use Email as Primary Key and use Identity Column as Auto number column or Not to use at all.
2)
I got idea for option 1 and option 2 from reading an article. Interest are going to Change. System will have set of predefined Interest.
Lets say if I go with Option 1:
As a user I selected Interest I1, I2
After sometime (few days) I update my profile. I unregister for Interest I1 but signup for Interest I3.. so now I have Interest I2, I3
In this case, should I remove the row from database for I1, and then add new row to database I3?
1) I've found that IDENTITY are the easiest to use for situations like this. I'd recommend starting them at 10000 to get a consistant length.
2) I'm curious -- I'd be interested in seeing the article you found a suggestion for Option2 in.
The idea is that you have a UserInterest table that contains each users interest -- so every time your interests change, you insert/delete from that table.
If you want a flat view of users with specific interests, just a matter of one join per interest ...
SELECT Username,
CASE WHEN I1.InterestId IS NULL THEN 0 ELSE 1 as Interest1,
CASE WHEN I2.InterestId IS NULL THEN 0 ELSE 1 as Interest2,
CASE WHEN Ix.InterestId IS NULL THEN 0 ELSE 1 as InterestX,
FROM Users
JOIN UserInterests I1 ON Users.UserId = I1.UserId
JOIN UserInterests I2 ON Users.UserId = I2.UserId
JOIN UserInterests IX ON Users.UserId = IX.UserId
1) Here is the article which gave indicated Option 2:
http://www.devx.com/dotnet/Article/20040/0/page/1
2) I liked your suggestion about Starting Identity with 10000 to get consistant length
3) If we use Web Services for Data Access and Business Layer, is it good / bad?
4) How can we apply some software design patterns ? I am looking into MVC but reading few things on web tells me that with .NET 2.0 it has got some issues. Most of our recent development is in .NET 2.0 ? Any ideas?|||
Another Database Design Issue:
We are designing a Shopping Cart forConfectionery Items. For Some Items Customer can select toppings and each toppings cost 0.50$.
Here is Our Sample Table
Products ( 0 = No and 1 = Yes )
ProductID ProductName ProductPrice CanShip CanDeliver
P1 Product 1 25 0 1
P2 product 2 50 1 1
How do we make handle Toppings ?
Lets Say there are 5 toppings options available. For Product 2, customer can choose toppings. How do we handle this in database design?
You'll need a Toppings table, and a table to represnt the one-to-many relationship that you are looking to model.
You will also, of course, need an Orders table. And I would be careful about the way you have the ProductPrice listerd; you might have issues if you decide to change the price...
|||I have read articles
1)http://www.sqlteam.com/item.asp?ItemID=2599,
2)http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=3804&FORUM_ID=5&CAT_ID=3&Topic_Title=Creating+a+table+of+information+based+from+other+t&Forum_Title=Developer
As per them using Identity Column as Primary Key is not good idea.
Now in our design we have used Identity Column for every table as Primary Key.
Let me write down table
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MI] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[State] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Location] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AppDomainName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessID] [nvarchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProcessName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ThreadName] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Win32ThreadId] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Message] [nvarchar] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FormattedMessage] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CreditCardNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpMonth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExpYear] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryID] [int] NULL ,
[ModelNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModelName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ProductImage] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDeliver] [bit] NOT NULL ,
[CanPickUP] [bit] NOT NULL ,
[CanShip] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Toppings] (
[ToppingID] [int] NOT NULL ,
[ToppingName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
|||
Arbitrarly putting an IDENTITY column on every table is what many of us refer to as an "ID-iot" design. Generally, IDENTITY makes a poor choice as a primary key because it is not predicible or verifiable. But that's a whole other discussion and is something that is very likely out of the scope of your project.
I will comment on your schema though ...
Your naming convention is poor. Data elements should follow ISO-11179 standards. Some examples:
- CanShip should be Shippable_Indicator
- DateCreated should be Creation_Date
- ShoppingCart should be ShoppingCarts
- DeliveryZip should be DeliveryRates
I see no constraints what so ever defined. do you want people to order -6 of an item?
You should avoid the MONEY type. Use DECIMAL instead.
Your model could use some improvement, especially with keys. Eg,
- RecordID is pointless
- An address should be (Cust_Id + Addres_Type)
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details
Alex... constraints are defined. However when I generated SQL script I forgot to check that in SQL Server. I will post script again tomorrow once I am at office.
1) Looking at above schema, can you suggest where can we remove Identity Column as Primary Key
2) I agree, we never followed any naming convention standard. Thanks for pointing out. Where can I see ISO-11179 standards? We will work on it.
3) Thanks for pointing Money / Decimal suggestion. Can you tell me why Money should be avoided?
4)
- I got your suggestion for RecordID.
- An address should be (Cust_Id + Addres_Type) -- What does it mean ?
- OrderTotal should not be in the Orders table. That can be calculated in a view by SUMing the Order_Details. -- How do I calculate OrderTotal along with Shipping and Tax from Order_Details
There is not anything intrisically wrong with using an identity or guid column, and in fact doing so can provide many benefits. If I were you, I'd research the topic more before taking a scapel to all of those identity keys.|||
Thanks pjmcb for looking into it. Now if I see comments from you and Alex, its contradictory. I will look into suggestions Alex made to me.
In the mean while here is the Schema with Constraints as I promised...
CREATE TABLE [dbo].[AddressBook] (
[AddressBookID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[FirstName] [varchar] (50) NOT NULL ,
[MI] [varchar] (2) NULL ,
[LastName] [varchar] (50) NOT NULL ,
[Address1] [varchar] (100) NOT NULL ,
[Address2] [varchar] (100) NOT NULL ,
[City] [varchar] (50) NOT NULL ,
[State] [varchar] (2) NOT NULL ,
[Zip] [varchar] (10) NOT NULL ,
[Phone] [varchar] (50) NOT NULL ,
[AddressType] [varchar] (20) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Categories] (
[CategoryName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[EmailAddress] [varchar] (50) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[CreationDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DeliveryZip] (
[ZipCode] [varchar] (10) NOT NULL ,
[Location] [varchar] (20) NOT NULL ,
[DeliveryRate] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Log] (
[LogID] [int] IDENTITY (1, 1) NOT NULL ,
[EventID] [int] NULL ,
[Category] [nvarchar] (64) NOT NULL ,
[Priority] [int] NOT NULL ,
[Severity] [nvarchar] (32) NOT NULL ,
[Title] [nvarchar] (256) NOT NULL ,
[Timestamp] [datetime] NOT NULL ,
[MachineName] [nvarchar] (32) NOT NULL ,
[AppDomainName] [nvarchar] (2048) NOT NULL ,
[ProcessID] [nvarchar] (256) NOT NULL ,
[ProcessName] [nvarchar] (2048) NOT NULL ,
[ThreadName] [nvarchar] (2048) NULL ,
[Win32ThreadId] [nvarchar] (128) NULL ,
[Message] [nvarchar] (2048) NULL ,
[FormattedMessage] [ntext] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderDetails] (
[ItemID] [int] NOT NULL ,
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[UnitCost] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Order_Toppings] (
[ItemID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (10000, 1) NOT NULL ,
[OrderDate] [datetime] NOT NULL ,
[CustomerID] [int] NOT NULL ,
[PaymentID] [int] NOT NULL ,
[ShipDate] [datetime] NOT NULL ,
[ShipMethod] [varchar] (20) NOT NULL ,
[ShipRate] [money] NOT NULL ,
[TaxAmount] [money] NOT NULL ,
[OrderTotal] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Payments] (
[PaymentID] [int] IDENTITY (1, 1) NOT NULL ,
[CardType] [varchar] (50) NOT NULL ,
[CreditCardNo] [varchar] (50) NOT NULL ,
[ExpMonth] [varchar] (50) NOT NULL ,
[ExpYear] [varchar] (50) NOT NULL ,
[AddressBookID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Prod_Toppings] (
[ProductID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Products] (
[ProductID] [int] IDENTITY (1000, 1) NOT NULL ,
[SubCategoryID] [int] NOT NULL ,
[ModelNumber] [varchar] (50) NULL ,
[ModelName] [varchar] (50) NOT NULL ,
[UnitCost] [money] NOT NULL ,
[Description] [varchar] (4000) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart] (
[RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[CartID] [varchar] (50) NULL ,
[Quantity] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[DateCreated] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ShoppingCart_Toppings] (
[RecordID] [int] NOT NULL ,
[ToppingName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[SubCategories] (
[SubCategoryID] [int] IDENTITY (100, 1) NOT NULL ,
[SubCategoryName] [varchar] (50) NOT NULL ,
[CategoryName] [varchar] (50) NOT NULL ,
[CanShip] [bit] NULL ,
[CanDeliver] [bit] NULL ,
[CanPickup] [bit] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Toppings] (
[ToppingName] [varchar] (50) NOT NULL ,
[ToppingPrice] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [DF_AddressBook_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_AddressBook] PRIMARY KEY CLUSTERED
(
[AddressBookID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Categories] ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customers] ADD
CONSTRAINT [DF_Customers_CreationDate] DEFAULT (getdate()) FOR [CreationDate],
CONSTRAINT [PK_Customers] PRIMARY KEY NONCLUSTERED
(
[CustomerID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Customers] UNIQUE NONCLUSTERED
(
[EmailAddress]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Log] ADD
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[LogID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
[ItemID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [DF_Orders_OrderDate] DEFAULT (getdate()) FOR [OrderDate],
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
(
[OrderID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Orders] UNIQUE NONCLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [PK_BillingInfo] PRIMARY KEY CLUSTERED
(
[PaymentID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [DF_Products_ModelNumber] DEFAULT ('') FOR [ModelNumber],
CONSTRAINT [DF_Products_Description] DEFAULT ('') FOR [Description],
CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED
(
[ProductID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [DF_ShoppingCart_Quantity] DEFAULT (1) FOR [Quantity],
CONSTRAINT [DF_ShoppingCart_DateCreated] DEFAULT (getdate()) FOR [DateCreated],
CONSTRAINT [PK_ShoppingCart] PRIMARY KEY NONCLUSTERED
(
[RecordID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [PK_SubCategories] PRIMARY KEY CLUSTERED
(
[SubCategoryID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Toppings] ADD
CONSTRAINT [PK_Toppings] PRIMARY KEY CLUSTERED
(
[ToppingName]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AddressBook] ADD
CONSTRAINT [FK_AddressBook_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
)
GO
ALTER TABLE [dbo].[OrderDetails] ADD
CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY
(
[OrderID]
) REFERENCES [dbo].[Orders] (
[OrderID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OrderDetails_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO
ALTER TABLE [dbo].[Order_Toppings] ADD
CONSTRAINT [FK_Order_Toppings_OrderDetails] FOREIGN KEY
(
[ItemID]
) REFERENCES [dbo].[OrderDetails] (
[ItemID]
),
CONSTRAINT [FK_Order_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO
ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Payments] FOREIGN KEY
(
[PaymentID]
) REFERENCES [dbo].[Payments] (
[PaymentID]
)
GO
ALTER TABLE [dbo].[Payments] ADD
CONSTRAINT [FK_Payments_AddressBook] FOREIGN KEY
(
[AddressBookID]
) REFERENCES [dbo].[AddressBook] (
[AddressBookID]
)
GO
ALTER TABLE [dbo].[Prod_Toppings] ADD
CONSTRAINT [FK_Prod_Toppings_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
),
CONSTRAINT [FK_Prod_Toppings_Toppings] FOREIGN KEY
(
[ToppingName]
) REFERENCES [dbo].[Toppings] (
[ToppingName]
)
GO
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [FK_Products_SubCategories] FOREIGN KEY
(
[SubCategoryID]
) REFERENCES [dbo].[SubCategories] (
[SubCategoryID]
)
GO
ALTER TABLE [dbo].[ShoppingCart] ADD
CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY
(
[ProductID]
) REFERENCES [dbo].[Products] (
[ProductID]
)
GO
ALTER TABLE [dbo].[ShoppingCart_Toppings] ADD
CONSTRAINT [FK_ShoppingCart_Toppings_ShoppingCart] FOREIGN KEY
(
[RecordID]
) REFERENCES [dbo].[ShoppingCart] (
[RecordID]
)
GO
ALTER TABLE [dbo].[SubCategories] ADD
CONSTRAINT [FK_SubCategories_Categories] FOREIGN KEY
(
[CategoryName]
) REFERENCES [dbo].[Categories] (
[CategoryName]
)
GO
|||
Good to hear you have constriants -- a lot of folks don't use them.
ISO-11179 has now officially been opened up for free. The wikipedia has a very basic article on it, so go search in there for it. I hear it has links to download the standards. The most useful part you will find is part-5. That contains lots of info on how to name your data elements.
The money type is completely worthless and an overall pain to work with. It's propietary and you always need to include a currency symbol infront of it. Makes selecting and querying for it a complete pain in the butt. If your products will be in different currencies, you should split that into two columns (Cost_Amt, Cost_Cur) and use ISO 4217 codes for currency. If you ever need to convert currencies, you can have a simple EXCHANGE_RATES table and do a quick join. Try doing that with MONEY type.
I've looked at your tables, here's where you can improve your keys and other things ...
[AddressBook] - I would name this Addresses or CustomerAddresses. Consider that a customer can have only one address of each type (which I'm assuming is user-defined, like "Home" or "Work"). Therefore, the PK should be (CustId, AddressType).
[Categories] - Who defines these and how will they be used? Generally I would say have CategoryName be the PK, but if it's part of a URL, that can get ugly. I'm assuming that they will be part of the URL, so I would recommend making a Category_Code, 3 or 5 digits (don't know how many or what categories there are), and codifying the categories.
[Customers] - This looks fine although who wants to be Customer #12? seed your identity at 10000, 100000 or whatever you think is one greater than the magnitude of customers (1000's of customers, use 10000, etc).
[DeliveryZip] - I would name this DeliveryRates. ZIP Codes are 5 digits, and only 5 digits (CHAR(5)). ZIP+4 consist of two parts: 5 and 4 digits. Pick which one you want to use, and use it consistantly. Also, I have no idea what Location is. Is this a Longitude/Lattitude? A city name? If you want to use this is a city lookup, that's fine, but include a state. Then you can auto-populate the City/State when customer enters ZIP code.
[Log] - I have no idea how your using log table, generally these are used to just dump logging/tracint data into. I would recommend having your log table be *off* primary table space and contain no primary key. It's just a conviennt way to have a flat file.
[Orders] - I would strongly recommend coming up with a keying scheme for your orders. This will make things monumentally easier for users of the system. It depends on how many orders you will be getting, and that sort of thing, but here is one I use
Y-DDD-SSSS (Y is last digit of year, DDD is day of year, SSSS is a sequence number for the number of orders in that day. should be estimated magnitude+1).
I would store Tax info at the item level -- not all items are taxable, and some items have different tax rates. You can make your view containing the totals really easily:
SELECT O.[OrderId], ..., I.Item_Amount, I.Tax_Total,
I.Item_Amount + I.Tax_Total + O.Shipping_Amount AS Grand_Total
FROM [Orders] O INNER JOIN
(SELECT [OrderId]
SUM([Item_Amount]) AS [Sub_Total],
SUM([Tax_Amount]) AS [Tax_Total]
FROM [Order_Items]
GROUP BY [OrderId]) OI ON O.OrderId = I.OrderId
[Payments] - Wrong name; should be StoredCreditCards, or something to that effect. Payments implies that it contains payments. The primary key should be (CustId, StoredCreditCards_Seq), Seq being a sequential number that starts at one for each customer. Or you could have the customers name their cards. Whjatever.
[Products] - I would also recommend coming up with something other than ProductId as your PK. Is there no SKU to use?
[ShoppingCart] - RecordID is completely unecessary. The PK should be CartID. You may want to consider making a random number/code up for Cart identifiers (I use LEFT(CAST(NEWID() AS VARCHAR(32)),8) By making them sequential and predicable, people can edit thgeir cookies and take control of other carts.
[ShoppingCart_Toppings] - Again record id is unecessary. PK Should be (CartId, ProductId).
|||Alex,Thanks for your suggestions.
We are looking into it. I don't know if you looked into the revised schema I posted or not.
[AddressBook]:
- We will rename it
- We can't use CustID + AddressType as PK. Since this is a shopping, person can have more than one Shipping or Billing Address. (Something similar to Amazon or BN who allows you to store multiple shipping and billing address)
[Categories]:
- Client Defined. We removed Identity Column and used Category Name as PK
[Customers]:
- Made change as per your suggestion
[DeliveryZip]
- Looking into it
[Log]:
- Error Logs.
[Orders]:
- Will have to think if we can do that or go with Identity starting with 100000 or something.
[Payments]:
- I think we don't need to rename it. Payment actually stores Credit Card info and amount charged on their Credit card. We never show customer their Credit Card info upon their next visit. Payment ID is referenced in Orders Table to know how customer has paid and how much was charged to him including everything.
Correct me if I am wrong.
[Products]:
- Client doesn't have SKU for products. We may go with Identity column starting with 1000 or something.
[ShoppingCart]:
- CartID is not sequential. It is random.
- CartID can't be PK coz, if I add more than 1 item into my cart, CartID is going to get repeated.
E.g.
CartID ProductID Qty
AAAA 1000 2
AAAA 2012 1
-- We have used IBuySpy Portal as our reference and some of the design is based on that. This indicates to us that we can't depend on design like that. Correct?
No comments:
Post a Comment