Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2013
    Posts
    1

    Choose between a table with many columns or with many rows

    Hi,

    I would like your opinions about a database design, the business model is :
    A user can say his avaibilities by specifying for each day of a week the time (range hour) he is available.
    For example : iam available monday between 9-10am, 10-11am,...but not at 11-12am and 1-2pm...

    The range are :

    • 9-10am
    • 10-11am
    • 11-12am
    • 1-2pm
    • 1-3pm
    • ..until 8-9pm



    I think about 2 design :

    1) Create a table "available" with one column "DAY" (day's name) and as many of the range columns as there ranges (here there are 9 ranges) and a user's FK column

    PROS : simple design and few tables, good performance i think but iam not sure (many columns...), if there are 1000 users there will be 1000 * 7 (differents days) : 7000 rows in available table.

    CONS :simple design but some duplications about column name, constraints...

    2) Create a table "available" with one column "DAY" and a FK of table "available_range" (which list all ranges possible) and a user's FK column.

    PROS : clean design i think, if one day i want add 7-8am range i can do it easily without change the structure table, however there are a limit number of range, so i maybe don't need to be generic in this case...

    CONS : Maybe the performance, if there are 1000 users there will be 1000 * 7 (differents days) * 9 (differents ranges) : 63 000 rows in available table.


    The real question is about the performance, if i choose option number 2, do you think that it can cause me some performance problem?
    Maybe you have other solutions..

    Thank you for your advice.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Option 2 is the cleaner design and 63000 rows will hardly give you any peformance problems .

    With Postgres you could even create efficient constraints to make sure that the ranges don't overlap for a single user.
    Last edited by shammat; 11-09-13 at 11:32.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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