MYSQL table structure

Question

I am trying to decide whether to create a global view table or 1 for each section. for example, Lets say I have products, categories and pages.

Do I have 3 tables e.g:

CREATE TABLE `tbl_products` (
`p_id` INT NOT NULL ,
`p_views` INT NOT NULL ,
INDEX ( `p_id` ) 
) ENGINE = MYISAM 

CREATE TABLE `tbl_categories` (
`c_id` INT NOT NULL ,
`c_views` INT NOT NULL ,
INDEX ( `c_id` ) 
) ENGINE = MYISAM 

CREATE TABLE `tbl_pages` (
`pg_id` INT NOT NULL ,
`pg_views` INT NOT NULL ,
INDEX ( `pg_id` ) 
) ENGINE = MYISAM

Or do I have 1 table storing all e.g.

CREATE TABLE `tbl_views` (
`view_id` INT NOT NULL ,
`view_type` VARCHAR( 10 ) NOT NULL ,
`view_views` INT NOT NULL ,
INDEX ( `view_id` ) 
) ENGINE = MYISAM

Where view_type is either products, categories or pages.

What would the advantages/disadvantages be of each solution?

Thanks in advance.

Answer 1

I really like the 1 table method, this keeps the database uncluttered, which i think is important.

Also, later on you may want to add another type, you wouldnt have to add another table, just a row with a different view_type.

Answer 2

Keep them in one table it is better, say at an instance you have to select no. of views of all 3 together it would be much easier and faster accessing a single table compared to three separate tables or say you have to update the no. of views of two or more view_type's again much better

Answer 3

In this case I suggest going with the one table. The only difference between the three tables is the type, thus I don't think they're necessary. As an added bonus to using only one table, should you add a new type of view, you don't need to create a new database table - you can simply use a new view_type key. However, if you needed different pieces of data for different types of views (e.x.: "pages" views needs column "x", and "categories" views need column "Y"), then you would want separate tables (this is called database normalization).

Answer 4

My decision on which method to use comes from how many records are needed and how likely the amount of information is to change. If you are just listing a few views, one table is good.

If the number of products + categories + pages is less than a thousand, I would combine them in one table. The advantage is that if you want to later add a field (example: an obsolete flag), you would only need to add it to one table. The disadvantage is that you are using (slightly) more space, i.e. storage & memory.

As the number of records gets larger, separating the lists can improve performance. If one type is far more numerous than the others, the smaller types can get bogged down in the parsing or maintenance of the table. If time treats the types differently, space can become an issue.

Example: If Products start to need more information (like style, price, or who is allowed to view it), but Pages and Categories never do, the table would reserve space that Pages and Categories will never use. Then, the Pages and Category records start to look silly mingled with Products.