Results 1 to 2 of 2

Thread: Database Design

  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Database Design

    Hi so i need help with my database design. I want my website to be as efficient as possible and fast. Now here is two of the designs i came up with and please tell me which one is better or recommend your own if mine are terrible :P. Thank you.

    Design #1
    bookInfo
    bookID(PK)
    userID(FK -> users)
    cityID(FK -> cities)
    + additional book info

    users
    userID(PK)
    provinceID(FK - province)
    cityID(FK - cities)

    cities
    cityID(PK)
    name
    provinceID(FK - province)

    province
    provinceID(PK)
    name

    And for this design I will have a separate database for each country. So for this one it will be fore Canada then I will have the same tables for anther database with USA etc. This is really easy for me to manage as I can look at separate countries etc.

    Here is the second design with everything under one database.

    Design #2
    bookInfoCanada
    bookID(PK)
    userID(FK -> users)
    cityID(FK -> cities)
    + additional book info

    usersCanada
    userID(PK)
    provinceID(FK - province)
    cityID(FK - cities)

    citiesCanada
    cityID(PK)
    name
    provinceID(FK - province)

    regionCanada
    regionCanadaID(PK)
    name

    bookInfoUSA
    bookID(PK)
    userID(FK -> users)
    cityID(FK -> cities)
    + additional book info

    usersUSA
    userID(PK)
    provinceID(FK - province)
    cityID(FK - cities)

    citiesUSA
    cityID(PK)
    name
    provinceID(FK - province)

    regionUSA
    regionUSAID(PK)
    name

    What I want here is a good design that will be fast and efficient. Thank you!

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    Fast and efficient depends on how you're going to use it. In general, normalization benefits updates, denormalization benefits queries. It also depends very much on your dbms and indexes. The best way to see which design is most efficient for your needs is to build them, populate them with realistic data, and profile them under realistic use conditions.

    As for the design, if my assumptions are correct, in your bookInfo tables userID implies cityID, and in your users tables cityID implies provinceID, i.e. you have functional dependencies on non-key attributes. You might want to normalize that.

    Also, I don't recommend your second design, it looks like high maintenance, e.g. if your schema changes you would have to repeat the same sequence of manipulations on each set of tables. You can query the first design by country if your provinces/regions are related to countries.

Posting Permissions

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