Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2007
    Posts
    15

    Review of my database so far

    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)
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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

  3. #3
    Join Date
    Nov 2007
    Posts
    15
    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

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.

  5. #5
    Join Date
    Nov 2007
    Posts
    15
    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?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.

  7. #7
    Join Date
    Nov 2007
    Posts
    15
    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

  8. #8
    Join Date
    Nov 2007
    Posts
    15
    Ok, I've done a revised DB with the merged tables. Take a look and give me your opinion
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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

  10. #10
    Join Date
    Nov 2007
    Posts
    15

    Review of database so far

    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
    Attached Files Attached Files

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Threads merged and moved to Database Concepts & Design

    Mike, please do not repost duplicate questions.
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •