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

    Unanswered: Database design question

    Hi,

    I need to store questions for two different subjects for three different levels (4, 5, 6). I'll most likely have separate tables for each subject.

    However for each subject, I'm wondering if I should (1) create a table for each of the three levels or if should I (2) create a single table all the levels.

    With option (1), I'll have two tables (english, math) with the level information stored in one of the columns.

    With option (2), I'll have six tables (english4, english5, english6, math4, math5, math6) without the level information (since the table itself already holds the level information).

    Which of the two options is the normal way to store data?

    P.S: The database is intended for web-based application.

    I hope the question is clear...

    Thanks in anticipation
    Last edited by pearl2; 09-09-04 at 05:33.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    Which of the two options is the normal way to store data?
    depends on whether you mean normal as in what is most common, what most people do, or normal as in normalized according to the rules of database design

    i would put everything into one table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by r937
    depends on whether you mean normal as in what is most common, what most people do, or normal as in normalized according to the rules of database design

    i would put everything into one table
    Thanks, r937!

    Normal as in what's the most common way of doing it.

    Added: What are the merits of putting everything in one table?
    Last edited by pearl2; 09-09-04 at 05:36.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    What are the merits of putting everything in one table?
    all your queries are a lot simpler
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    I see.

    I've some concerns:

    1) Are there problems with concurrent access to the same table?

    2) In the event of table corruption, everything in that single table is affected. Is this an issue at all?

    Thanks again and cheers

  6. #6
    Join Date
    Sep 2004
    Posts
    2
    I am not sure if I got correctly what you mean. But anyway I would suggest the following structure (You may need to modify the structure a little bit to let fit to your needs).

    #
    # Table structure for table `level`
    #

    CREATE TABLE `level` (
    `id` int(11) NOT NULL default '0',
    `level` varchar(10) NOT NULL default '',
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
    ) TYPE=MyISAM;


    #
    # Dumping data for table `level`
    #

    INSERT INTO `level` (`id`, `level`) VALUES (0, '5');
    INSERT INTO `level` (`id`, `level`) VALUES (1, '6');
    INSERT INTO `level` (`id`, `level`) VALUES (2, '4');
    INSERT INTO `level` (`id`, `level`) VALUES (3, '6');

    # --------------------------------------------------------

    #
    # Table structure for table `questions`
    #

    CREATE TABLE `questions` (
    `id` int(11) NOT NULL default '0',
    `english` varchar(255) NOT NULL default '',
    `math` varchar(255) NOT NULL default '',
    `level_id` int(11) NOT NULL default '0',
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `questions`
    #

    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (0, 'English1', '', 0);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (1, 'English 2', '', 1);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (2, 'Englsih 17', '', 2);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (3, '', 'Math 80', 3);

    Ahmed

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pearl2
    1) Are there problems with concurrent access to the same table?
    no, no more so than if there were many tables

    2) In the event of table corruption, everything in that single table is affected. Is this an issue at all?
    no, no more so than if there were many tables

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Wonderful, great thanks r937!

  9. #9
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Quote Originally Posted by aadaileh
    I am not sure if I got correctly what you mean. But anyway I would suggest the following structure (You may need to modify the structure a little bit to let fit to your needs).

    #
    # Table structure for table `level`
    #

    CREATE TABLE `level` (
    `id` int(11) NOT NULL default '0',
    `level` varchar(10) NOT NULL default '',
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
    ) TYPE=MyISAM;


    #
    # Dumping data for table `level`
    #

    INSERT INTO `level` (`id`, `level`) VALUES (0, '5');
    INSERT INTO `level` (`id`, `level`) VALUES (1, '6');
    INSERT INTO `level` (`id`, `level`) VALUES (2, '4');
    INSERT INTO `level` (`id`, `level`) VALUES (3, '6');

    # --------------------------------------------------------

    #
    # Table structure for table `questions`
    #

    CREATE TABLE `questions` (
    `id` int(11) NOT NULL default '0',
    `english` varchar(255) NOT NULL default '',
    `math` varchar(255) NOT NULL default '',
    `level_id` int(11) NOT NULL default '0',
    PRIMARY KEY (`id`),
    KEY `id` (`id`)
    ) TYPE=MyISAM;

    #
    # Dumping data for table `questions`
    #

    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (0, 'English1', '', 0);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (1, 'English 2', '', 1);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (2, 'Englsih 17', '', 2);
    INSERT INTO `questions` (`id`, `english`, `math`, `level_id`) VALUES (3, '', 'Math 80', 3);

    Ahmed
    Thanks, Ahmed! Will use it for reference.

Posting Permissions

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