Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Oct 2011
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating Dynamic amount of tables in MySQL

    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.

  • #2
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    9
    Thanks
    1
    Thanked 2 Times in 2 Posts
    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.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    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:
    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;
    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.

  • #4
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    9
    Thanks
    1
    Thanked 2 Times in 2 Posts
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    Quote Originally Posted by zergi View Post
    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.

  • #6
    New to the CF scene
    Join Date
    Nov 2012
    Posts
    9
    Thanks
    1
    Thanked 2 Times in 2 Posts
    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.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,567
    Thanks
    78
    Thanked 4,385 Times in 4,350 Posts
    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, ...")
    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.


  •  

    Posting Permissions

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