Critique My Database Design

suarve · 26 Dec 2008 at 20:58

Hi,

Can someone please critique my database design? I'm doing a personal project to force me to use php more and am making an online store selling computer software.

The only thing I'm a bit unsure on is the Wishlist table. I'm also planning on having a price listing on my page (E.g. £20 - £40 (3 Products)), will will storing the price as a single value allow me to do this easily (can't think how to achieve this effect).

Anyway, the design:

unknowndomain · 27 Dec 2008 at 10:28

I am no expert so I cant really comment on the design, but from a graphic designers perspective it would be better to have the arrows pointing from the primary key to the relevant foreign key, so it doesn't require you to read the whole list....

Also the two tables at the bottom are not linked to anything...

As an additional idea it might be nice to have a database of serials for digital download encrypted and then decrypted also.

RockLobster · 27 Dec 2008 at 11:11

unknowndomain said:
I am no expert so I cant really comment on the design, but from a graphic designers perspective it would be better to have the arrows pointing from the primary key to the relevant foreign key, so it doesn't require you to read the whole list....

That's how Visio can portray DB diagrams it is perfectly acceptable. And the reason the bottom two tables are not linked is one is reference data and the other is a config table.

Your design can only store one billing address, one customer address and one payment method because of this there is no way you can have a history of these. My advice is have another table called Address in this table have your typical address fields but also have a field called AddressType if the type is H, it is a home address if it is a B it's a billing address. You will need a start and end date so you can store a history of these addresses. When the end date is null that will show your current address. If someone changes their address you just put an end date in (today's date) and create a new record. Get the idea now?

You may want to do the same with payment methods as you know most online retailers store a history of payment methods. Order isn't shown as a foreign key in Order. Keep your table names consistent either use underscores to separate words or don't. Change the field name Suspended to Status. Status can either be Active or Suspended.

I can't look any more I need to pop out. Hope this helps.

PS - the price listing bit I'm not sure what you mean? Do you mean like OCUK? - http://www.overclockers.co.uk/productlist.php?&groupid=701&catid=56&sortby=priceAsc

Dolph · 27 Dec 2008 at 12:06

I'd agree with Rocklobster on the comments, definitely make those changes as it makes it much more future proof and provides better reporting potential.

You might also want to add extra details to the manufacturer table (eg address, customer services, commercial services and technical support phone numbers and so on), and you might also want to consider a distributor setup as well as manufacturer because many businesses buy through suppliers rather than direct.

With the price listing thing, that's easy to achieve with single prices, so you don't need extra fields for that. from an SQL point of view you'd use the 'between' operator.

suarve · 27 Dec 2008 at 13:31

Thanks for comments people.

As for the price thing I meant like a competitor (can't link here). E.g.

0-20 (2 Products)
20-40 (3 Products)
40-60 (9 products)
60 - 80
80 plus

I struggling as to how I'd genrate this list dynamically (using ASP). My initial thought would require me to open multiple recordsets to achieve the list, but there must be a better way. Have been lookin into stored procedures on the SQL Server - although I'm using them for more simple queries.

The two tables at the bottom are simply to check for a discount code during checkout and for misc shop settings (E.g. admin can display a For Sale pane on the site). No need to link these to any tables yet - although I may work on making the Discounts system a bit more elaborate.

Is the wishlist table acceptable? As that still really concerns me for some reason.

RockLobster · 27 Dec 2008 at 13:40

suarve said:
Thanks for comments people.

As for the price thing I meant like a competitor (can't link here). E.g.

0-20 (2 Products)
20-40 (3 Products)
40-60 (9 products)
60 - 80
80 plus

You can do this using an SQL query but it could be a bit of a pain to write. Here is a link which may help you - http://www.craigsmullins.com/ssu_0899.htm

Nimble · 30 Dec 2008 at 11:33

Your naming looks a bit inconsistent:

- Sometimes you use '_' between words and sometimes no space at all (which RockLobster already mentioned).
- Some tables are named as plurals and some not.
- Some fields are prefixed with abbreviations of the table name when it seems to be unnecessary (OSName, CatDescription, etc.).

The Category table has a relationship with itself which isn't displayed in the diagram. Several foreign keys aren't marked as foreign keys. The direction of the arrows is inconsistent.

Seems a bit strange linking a customer to a single OS.

WishListID seems to be redundant, CustomerID and ProductID as a composite key would work. Apart from that I don't see anything wrong with the WishList table, what are you unsure about with it? I notice that Order_Items could also work with a composite key instead.

jon86 · 31 Dec 2008 at 03:23

If using SQL Server, you could possibly write a function to do it. I haven't tested this but it should work, although probably quite inefficient.

-------------------------

Create Function fnGetProductCountByPriceBand
(
@SmallValue int,
@BigValue int,
@Criteria nvarchar(20)
)
Returns int
As
Begin

Select Count(ProductID)
From Product
Where UnitPrice BETWEEN @SmallValue AND @BigValue
And Name LIKE '%' + @Criteria + '%'

Return

End

Go

-------------------------

Select fnGetProductCountByPriceBand(0, 20, 'Criteria') As ProductCount0,
fnGetProductCountByPriceBand(21, 40, 'Criteria') As ProductCount1,
fnGetProductCountByPriceBand(41, 60, 'Criteria') As ProductCount2,
fnGetProductCountByPriceBand(81, 999999999999, 'Criteria') As ProductCount3
From Product

suarve · 6 Jan 2009 at 22:32

jon86 said:
If using SQL Server, you could possibly write a function to do it. I haven't tested this but it should work, although probably quite inefficient.

-------------------------

Create Function fnGetProductCountByPriceBand
(
@SmallValue int,
@BigValue int,
@Criteria nvarchar(20)
)
Returns int
As
Begin

Select Count(ProductID)
From Product
Where UnitPrice BETWEEN @SmallValue AND @BigValue
And Name LIKE '%' + @Criteria + '%'

Return

End

Go

-------------------------

Select fnGetProductCountByPriceBand(0, 20, 'Criteria') As ProductCount0,
fnGetProductCountByPriceBand(21, 40, 'Criteria') As ProductCount1,
fnGetProductCountByPriceBand(41, 60, 'Criteria') As ProductCount2,
fnGetProductCountByPriceBand(81, 999999999999, 'Criteria') As ProductCount3
From Product

Totally forgot about this thread.

Think I'm going to write a stored proc like the above and hardcode the price ranges into the page. This way I'll only need to use the stored proc.

I'll make a dynamic list based on the current prices in table if i get time

suarve · 6 Jan 2009 at 22:35

Also, purely out of interest, can anyone get the following function to work? Its supposed to return a single value (a singleton??????) and is looks more efficient than opening a recordset:

Code:

' ... Open connection

strSQL = "SELECT ? = ISNULL(SUM(Price*Qty), 0) FROM Products"
Response.Write "Inventory Value : " & SingletonQuery(objCN, strSQL)

Function [COLOR=Red]SingletonQuery[/COLOR](ByRef objCN, ByVal strSQL)
	Set objCmd = Server.CreateObject("ADODB.Command")
	Set objCmd.ActiveConnection = objCN
	objCmd.CommandText = strsql
	objCmd.CommandType = adCmdText
	objCmd.Parameters.Append _
		objCmd.CreateParameter(,adVariant,adParamOutput)
	objCmd.Prepared = True
	objCmd.Execute
	SingletonQuery = objCmd.Parameters.Item(0)
	Set objCmd = Nothing
End Function

Taken from http://www.somacon.com/p139.php

cjmUK · 7 Jan 2009 at 12:12

You could make more used of Compound Keys, for example on the WishList table and arguably other table too.

Is this system ever to see the light of day or is it just an intellectual exercise? In reality you are unlikely to record payment details - you'll just farm the payments out to a 3rd party service (ProtX, WorldPay, Paypal) - you don't want to be recording card numbers if you can avoid it.

The OS table probably needs expanding. OS's arguably belong to different categories - x64/x86/ia64, Windows, Mac, *nix, others. Customers are going to want to search in a variety of ways... all windows software, Vista software, x64 software, server software... etc.

The Customer table doesn't want to be linked to the OS table - customers might have a variety of platforms, and regardless, you don't care.

You also need to normalise the Customer Table further. Strip out Address as another table. Add a Customer Address table:

CustomerID
AddressTypeID
Active (bool/bit)

and arguably an AddressType table:

AddressTypeID
AddressTypeDesc (Billing Address, DeliveryAddress)

CustomerID.SurName should really be CustomerID.LastName, and you are missing a Title column.

If you are going to 'suspend' accounts, you might want to record the reason for suspension.

Another non-essential, but extremely useful addition would be a Transaction Log to record important transactions:

New User
New Order
Cancelled Order
User Suspended
etc...

Very useful for historical tracking, traceability and debugging...

I think I should stop there; no point in going overboard so early on!

HTH

Chris