Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Which design should I use - depth or breadth?

    Hi,

    I can't decide which of the following two designs to adopt:

    Code:
    Design 1
    CREATE TABLE scores (
      userid MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      subject_id CHAR(1) NOT NULL,
      done1 MEDIUMINT(8) UNSIGNED,
      correct1 MEDIUMINT(8) UNSIGNED,
      done2 MEDIUMINT(8) UNSIGNED,
      correct2 MEDIUMINT(8) UNSIGNED,
      done3 MEDIUMINT(8) UNSIGNED,
      correct3 MEDIUMINT(8) UNSIGNED,
      PRIMARY KEY  (userid, subject_id),
    );
    Code:
    Design 2
    CREATE TABLE scores (
      userid MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0',
      subject_id CHAR(1) NOT NULL,
      level ENUM('1', '2', '3') NOT NULL DEFAULT '1',
      done MEDIUMINT(8) UNSIGNED,
      correct MEDIUMINT(8) UNSIGNED,
      PRIMARY KEY  (userid, subject_id, level),
    );
    Assuming the number of levels is definitely fixed at 3 and there are 10 subject ids.

    With Design 1, every new member requires the insertion of 10 rows (because the level information is already built into each row).

    With Design 2, every new member requires the insertion 30 rows (10 rows for each level, so 10 x 3 gives 30 rows).

    Should I choose Design 1 because it's less work on the database with only 10 insertions?

    Thanks in advance
    Last edited by pearl2; 03-13-09 at 08:35.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Should I choose Design 1 because it's less work on the database with only 10 insertions?
    I vote for design 2 because it can cope with a change in the number of levels. I know you said it's fixed at 3 but I've heard that before. I think it makes it easier to code as you don't need to have SQL like "level1 < 0 and done1=1 or level2 < 0 and done2=1 or level3 < 0 and done3=1". It will be marginally slower but I don't think it would be noticeable - how many rows in total are you talking about?

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, mike_bike_kite

    I started with Design 1, it seemed quite a logical choice. But I wasn't very comfortable with it because, as you said, it makes adding new levels harder and also more complicated queries.

    So I switched to Design 2. Again, it appeared to the right thing to do - more fluid as new levels can be added easily.

    But I'm concerned about the 3 times more rows that have to be added for every member with Design 2. I'm also wondering whether it's a bit duplicative because every subject_id has 3 corresponding levels.

    If there're 1000 members, Design 1 adds 10,000 rows while Design 2 adds 30,000 rows - that's 20,000 rows more.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You can't compare number of rows without considering how wide each row will be.
    There is going to be pretty much the same amount of data either way round, but one design is far easier to use than the other...
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Ah, thanks for that perspective

    I've so far equated more rows to "more troubles ahead for the database" - like can the database cope if there are so many rows..? I guess that shouldn't be a concern if the fields are properly indexed. Am I right?

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pearl2
    Thanks, mike_bike_kite But I'm concerned about the 3 times more rows that have to be added for every member with Design 2. I'm also wondering whether it's a bit duplicative because every subject_id has 3 corresponding levels.

    If there're 1000 members, Design 1 adds 10,000 rows while Design 2 adds 30,000 rows - that's 20,000 rows more.
    30k rows is a drop in the ocean to most databases. Why not quickly set up the two tables with data and then try comparing the two methods for performance. I'll bet you hardly notice any difference. Then try writing a few queries on both versions of the table. Then imagine a year or two in the future and alter both the tables and the queries to use 4 levels. I'd hate to make the database design a limiting factor (ie 3 levels and that's it) especially when it's equally easy to make it more flexible in the 1st place.

  7. #7
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    30k rows is a drop in the ocean to most databases
    That's was a nice way put it. I've no worries now. Will choose Design 2.

    Thank you
    Last edited by pearl2; 03-13-09 at 13:01.

Posting Permissions

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