Which design should I use - depth or breadth?

pearl2 · #1 (**permalink**) 03-13-09, 07:32

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

mike_bike_kite · #2 (**permalink**) 03-13-09, 07:47

Quote:

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?

pearl2 · #3 (**permalink**) 03-13-09, 07:57

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.

gvee · #4 (**permalink**) 03-13-09, 08:16

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...

pearl2 · #5 (**permalink**) 03-13-09, 08:49

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?

mike_bike_kite · #6 (**permalink**) 03-13-09, 10:16

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.