is there such a thing as nested tables?

[david_kw]

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

[JimmyS]

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.

[felgall]

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.

[david_kw]

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

[guelphdad]

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.

[david_kw]

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