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 6 of 6
  1. #1
    Senior Coder
    Join Date
    Nov 2006
    Posts
    1,000
    Thanks
    0
    Thanked 0 Times in 0 Posts

    is there such a thing as nested tables?

    Hello there,

    I had a question and was hoping someone could help me brainstorm on how this could be done.

    I have a situation where I have lots of users. I have a table that saves various information like the last time they logged in, the date they created their account, etc.

    Each of these users also has data associated with them that would need a table. For example, if each user had their own ToDo list.

    So my question is what is the best way to store that in a database? My first thought (coming from my programming background) is that I would add a column called todolist to the first table and that column would have a full nested table inside each cell that would store the list of ToDo items along with their priority and completion status, etc.

    But I don't see any way to do that in mysql. So my second thought is to create table for each individual user called todolist_username.

    Unfortunately that would give me potentially thousands of tables in my database. Is that the right way to do it or is there another option I'm unaware of?

    david_kw

  • #2
    New Coder
    Join Date
    Oct 2006
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I always make one table that is:
    userid|userinfo

    Then another table as:
    ToDoID|userid|ToDoInfo

    Then you only have two tables.

    So all todo list records for all users are in one table and they are identified by the userid. And you just search for records in the todo list table that match the userid of the user you want.
    Last edited by JimmyS; 01-11-2007 at 08:03 PM.

  • #3
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    If you start out by defining all of the data you want to store and the relationships and then apply the normalization rules the number and organization of the tables will resolve itself.

    See http://www.felgall.com/mfgen3.htm for info on normalizing a database.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #4
    Senior Coder
    Join Date
    Nov 2006
    Posts
    1,000
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I could see that. Which brings me to a question. Would it be better to have 1,000 tables that are 1,000 rows long or one table that is 1,000,000 rows long in a database?

    I was thinking the former would be more efficient and flexible. If there were just some nice way to tuck the 1,000 tables out of sight. I suppose it could be in a different database. But can you merge tables from seperate databases?

    david_kw

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    the latter is superior when you want to gather stats on all your users. The former you have to UNION or JOIN 1000 tables, messy.

    By the way, 1,000,000 rows is inconsequential in size as far as mysql is concerned when properly tuned and the tables are indexed well.

  • #6
    Senior Coder
    Join Date
    Nov 2006
    Posts
    1,000
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, it sounds like there really is no way to nest tables or do something similar. I think I'll go with the one table for now and see how it goes.

    I read the database normalization article, but I think I'll have to reread it when I have more time since I think some of it went over my head.

    Thanks all for the help.

    david_kw


  •  

    Posting Permissions

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