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.
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.
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!
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
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.