Creating Dynamic amount of tables in MySQL

[chettyharish]

Ok I am making a project on a DTH management system. I am using MySQL DB and Java GUI. The problem is that I have to make the DB such that everytime I add a user, a set of tables must be inserted (e.g 30 days, one table for each day) to add values. This new table will have the same format for all users, the only thing is the column values will be changing. It's like a Matrix where top horizontal row is for timings and left vertical row for channel name and if a user is watching a show on channel 9 at 9pm, then the cell (channel9, 9) will have a value 1, else 0. This has to be done for each day.

My question is simple how do you do this!! Secondly what will be the load on the system. As in the GUI must not crash or take a huge amount of time to access the data.

Is there any other method to do this more easily and efficientl.

Please help.

[zergi]

Well, you definitely should not create a new table (or more) for each user.

You need 3 tables in general.
First, a table for users, `users`. With columns: id, name ... and whatever else.
Second, you need a table for channels, lets call it `channels`. It will have two columns: id, name.
The third table, lets call it `channel_watch`, will have columns: id, user_id, channel_id, watch_time

Now, when you add a user, add the row to the users table.
When you add a user watch, check, if channel exists in channels table, if it does, grab its `id` if not, add a row and then fetch the `id` column value of the inserted row.
And finally, insert one row to `channel_watch` table with known user_id, channel_id and the time of the show.

All the ID columns are usually AUTO_INCREMENT, so they are generated automatically when new record inserted.

Also, reading a book on relational databases will not hurt.

[Old Pedant]

Zergi is more than right.

He didn't make his point strong enough, if anything.

I have to make the DB such that everytime I add a user, a set of tables must be inserted (e.g 30 days, one table for each day)

This is probably *THE VERY WORST DESIGN* I have seen for a database in the last 14 years of answering database questions in various forum.

The *ONLY* point where I disagree, in a minor way, with Zergi is here:

id, user_id, channel_id, watch_time

The id column there is completely unneeded. It serves no purpose, at all.

For this table, the combination of the other three fields forms a perfect PRIMARY KEY and should be so code:

CREATE TABLE channel_watch (
    user_id INT NOT NULL,
    channel_id INT NOT NULL,
    watch_time DATETIME NOT NULL,
    PRIMARY KEY ( used_id, channel_id, watch_time ),
    CONSTRAINT FOREIGN KEY user_id REFERENCES users(user_id),
    CONSTRAINT FOREIGN KEY channel_id REFERENCES channels(channel_id)
) ENGINE INNODB;

[zergi]

Often in real world apps it is more comfortable to work with a single column id, rather that with multi-column key (adding unique key on these columns).

But from the clear database design point of view the compound primary key is of course optimal for this task.

[Old Pedant]

Originally Posted by zergi

Often in real world apps it is more comfortable to work with a single column id, rather that with multi-column key

Absolutely agree! But that is only true if you will *USE* that single column id.

In this case, where the table you (correctly!) proposed is a many-to-many table, I can't see any case where you would need to *REFER* to records therein. (If you can make such a case, I'll reverse my position, of course.)

Mind you, it is *NOT* a major point, and that's why I said I disagreed in only a minor way. It's not going to hurt to have an added auto_increment ID there; I just don't see it as adding any value.

Glad to see another DB fan join in here. Welcome!

[zergi]

Glad to see another DB fan join in here. Welcome!

Thanks I'm also glad to join the forum.

What I meant by comfortable is, for example, when you have an html list of rows and you need to build a link to delete the row. It is easier to use a link like 'delete?id=123' than passing those three keys all around.

But, as I said, it's really more about the application design / convenience, rather than a database structure.

[Old Pedant]

Good point about the delete. I don't see that as an issue for the project he is contemplating, but if it were, your answer would be right. (Hey, I did say "If you can make such a case, ...")