Could someone offer suggestions on how to improve the database or the general structure of it?

All suggestions are welcome!

(Its an online retail project selling DVD's, CD's & Games)

No I wont do your assingnment for you... but I do think you have some problems with your order

You are missing at least one RI check
I don't understand the stuff in a the order table such as product type and product name.. isnt that part of the order details?
can a customer only order a single item per order
becasue od th way you have modeled your products you have soem confusion between CD's & DVD's. as far as a shop would be concerend they are just products.. ie I'd have the main common detials int he same product table, and then have child tables hanging off that product table that deal with elements that are specific to a CD or DVD or wahtever. the way you are settign this up you are going to have a problem if say the shop decides to sell downloads (anothe rnew table), or mp3 players or wahtever)

whatr is the differnece between an Employee and and a user.. coudl your coalesce these two entities into one item and the have a child table (if neccdesary to hold the unique data relevant to a customer or employee)
what happens if you have a employee who is also a customer, do you really want the users/employee to enter details twice.. its going to create a problme when the person moves address..... does the person doing the update realise that there are two addresses to maintian.
what happens if the user has more than one address (say work & home)
what happens if you need more than two telephone numbers (eg home, work alt work, alt home, cell, skype....)
what happens if the customer requests a order to be delivered to a different address that on file.

ok so thats a lot of questions, some of the answers to which may be.. I don't need to support that, or its irrelevant in what I'm trying to achieve, which is fair enough

I think you are heading in the right direction, but I do think there is a bit of confusion in your mind in where to store information. and how to handle the requirement to reduce storing the same or similar information in different discrete containers

..keep up the good work

Thanks for the quick responce

1) The only reason I wanted to keep the users + employees separate is because I want to have a separate frontend for the users and admin. What’s your opinion on it??

2) I defiantly want the customers to have more than one item per order, what do I need to change to make that happen

3) I never thought of adding a different delivery address (GREAT POINT!)

4) I was thinking about putting the CD, DVD, Game tables into one table, what do you think?

5) The temp users table has no relationship because that will be used as a feed table for the users once they confirm via an email link their details will be passed into the users table

its not my homework..... what do you think?

those questions were posed to attempt to make you think about what you need to complete this project.

as regards separate front end thats down to the application front end.. which isn't the same as the backend / data store. there is no requirement to have one form to one table.

Thats down to how the data is extracted and manipulated. a good application starts with a good physical design which starts with a good grasp of what is required.

In larger server based applications often the data is presented as views, which could be a mix of data from more than one table, or they could be a specific snapshot of what a specific user or usergroup is allowed to view. for example an employee table may have a lot of sensitive data in in.. you may grant a view to say allusers to look at the employees names and work phone number, you may grant access to more senstivie data (eg home address and home phone number) to thier immediate line manager, you may grant full access to everything int he employee table to the personnel / HR function. granting full access to all data to all employees may lead you to at best a Data Protection Act violation and at worst horrible legal implications if an employee got harrassed at work becauase of slipshod design.

Its not really my "homework" either, its just the backbone for my degree project and want to get as solid of a database as possible. I'm just looking for advice and again thanks for what you've said so far.

In relation to the Order issue, Why can the user only get 1 item per order? I thought having the order details would get round this problem?

whats the pk on the order details?
what is it that makes an order detail unique?
think through your model with some data. will the model support the operation you need

it may be the backbone of your degree project.. but its still homework as far as I see it.

If I was to make another field Orderdetails and make that the PK would that solve the problem? I thought having the orderid posted from the orders table would make each order unique would address that issue but obviously after looking at it again I was wrong

Ok, I've done a revised DB with the merged tables. Take a look and give me your opinion

I'm not going to look at your db....

I#d suggest if you want a review then Id suggest you talk to your peers, or your lecturer or tutors, they after all are closer to waht is actually being requested.

I think you need to go through each table and try to work out if the information is being stored there correctly, if there are no redundancies of data (ie the same element being stored in more than one place).

If you are at all hesitant then I'd suggest you look at something like Paul Litwins bit on normalisation & relational theory on Rudy (R937's) website

Hi,

I'm doing a database for a CD, DVD, Game website could you take a second to review it and give me suggestions to improve things.

I've included my old and revised database to see what if done

Threads merged and moved to Database Concepts & Design

Mike, please do not repost duplicate questions.

mike, you will increase the odds of people looking at your diagram if you don't put hurdles in their way

do a screen capture (Alt-PrintScreen), open Windows Paint, paste, save as gif, then upload the gif

not everybody has Word (and not all of those who do feel like firing it up just to look at a stretched image)

but everybody's got a browser, and gifs display nicely in browsers with no hoops to jump through

er, i mean, hurdles to jump over (sorry about the mixed metaphor)