Results 1 to 15 of 15
  1. #1
    Join Date
    May 2012
    Posts
    8

    Unanswered: Database Design Advice

    Hi

    I am looking for a bit of advice on my database design. I am currently teaching myself database modelling, SQL normalisation etc.

    I have designed a database for a online book store where members can also leave reviews.

    The only 2 assumptions I have made are that all books are associated to one genre and only have one author. As the database is not going to be used live and it is more of a theroricital exercise to design a database and populate it and see how well I can generate SQL etc, I have made these assumptions.

    Anyway I have attached the ERD diagram I have come up with and I am wondering if it is correct. If someone could have a look and give advice it be appreciated.

    Just want to see if my entites are correct and the are the attributes of each table right too.

    Thanks
    Attached Thumbnails Attached Thumbnails ER_Bookstore Revised.jpg  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why does Review need a Review ID?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2012
    Posts
    8
    Just from the studying I have done, I thought all tables had to have a field as a primary key so as a id field is usually best way of doing this that is why I included it.

    My logic could be totally wrong. Looking at it now I could see why it may not be necesaary as it not being used to link it to any other table.

    Should I remove it?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here's a hint

    entity relationship diagrams are used in the logical design of the database

    surrogate keys are introduced, if needed, during physical design later

    you shouldn't be thinking about tables yet

    at this point, just concentrate on natural keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2012
    Posts
    8
    Thanks for the advice

    However the diagram that I posted was basically the way I was going to physically construct the database, with a few minor additons to the customer table.

    I had rougher ER diagrams but the one above it what I settled on, have to do a double check to ensure they are normalisated and that - sometimes a bit confusing to me - but I did think that what I created above was mainly ok.

    I know in a real life senario it would not be totally suitable but I want to practice the SQL, queries joins etc. I am fairly ok with the SQl side and that just the database design I sometimes struggle with.

    So is the design with arrtibuts listed not suitable at all

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this thread could take one of two directions, your call

    1. it's a homework assignment and you want to score well

    2. it's a hobby database and you want to practice

    let me know which way you want to go
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2012
    Posts
    8
    As I said I am teaching myself database modelling and sql. So I want to practice and get it right. I hope to go on and maybe take a career change and move into DBA etc I know I will need to do professional courses and that but want to get a good grounding.

    However I do not see the difference surely if I am teaching myself and practicing your answer be no different to a student learning and asking for advice.

    Not being snotty but I am doing this myself and want to get it right, Im not a student submitting it but I want to be able to to database design well enough that if I took a professional cousre and I submitted this as an assignment it would be good enough to get a good mark.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so that's answer 2, right?

    okay, in that case, how many times can a given customer review the same book?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2012
    Posts
    8
    As I thought I indictaed in the diagaram a customer can only leave 1 review per book but a book can have many reviews.

    So a book can have many reviews but a review is only associated to one book. Hence I had the book to review 1:M and the customer to review 1:M

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by redverse22 View Post
    So a book can have many reviews but a review is only associated to one book. Hence I had the book to review 1:M and the customer to review 1:M
    so the PK should be (customer_id,book_id) and you don't need the review_id column at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2012
    Posts
    8
    Ah I see what you mean now. I should have seen that and knew why it was not necessary.

    Thanks so by removing the Review_ID and letting customer_id and book_id be the primary key it still ensures the table meets 3NF.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the issue of 3NF is only marginally related to whether one uses a surrogate key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    May 2012
    Posts
    8
    Ok thanks.

    Actually I just noticed something now and want to double check.

    On my review table I have it linked to customer table by using the customer_id, however really it be user name or you can add a column for your own name to identify reviews by.

    So if I'm not sure the way to go about without totally messing up all the work I have done on tables as was going to go on and create database and see how I go.

    So would it be better off to leave customer_id and add another column where customer can add what name they want to review under or what?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by redverse22 View Post
    So would it be better off to leave customer_id and add another column where customer can add what name they want to review under or what?
    as long as you make customer_username the PK, you'll be fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    May 2012
    Posts
    8
    Of course silly of me. I swear my head is all over the place at the moment.

    Thanks very much for all the advice it is very much appreciated.

Posting Permissions

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