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

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?

all your queries are a lot simpler

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

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

no, no more so than if there were many tables

no, no more so than if there were many tables

Wonderful, great thanks r937!

Thanks, Ahmed! Will use it for reference.