[rhunter007]

Here's my situation. I have a set of users. Each user has a number of "threads" of data. Each thread has a number of "entries". I want to know how to represent this in MySQL.

One option I see is to create a users database. Then each user has a table with threads. And also, each individual thread has its own table to house the entries. But this seems strange to me. I would be putting everything at the "top level" and it seems like it would be very cluttered.

Another option might be to have a top-level database, with a singe user table. Each element of the user table is a "thread". Each thread points to a subtable of entry tables. Conceptually this seems cleaner to me.

Which way should I do this and why? And, if the second way, then how?

Thanks.

[hyjacked]

I would suggest:
User table
-user_id, name, etc

Thread table
-thread_id, user_id, entry_id, thread_title

Entry table
-entry_id, thread_id, entry_title, entry_data

User table contains all users.
Thread table contains all threads.
Entry table contains all entries.

If you need more information to understand this, feel free to post back.

[YourPHPPro]

Originally Posted by rhunter007

then how?

Adding to what hyjacked said, here's the SQL for what he said:

# Database : `test_user`
#

# --------------------------------------------------------

#
# Table structure for table `entry`
#

DROP TABLE IF EXISTS `entry`;
CREATE TABLE `entry` (
`entry_id` int(3) NOT NULL auto_increment,
`thread_id` int(3) NOT NULL default '0',
`entry_title` varchar(50) NOT NULL default '',
`entry_date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`entry_id`),
KEY `thread_id` (`thread_id`,`entry_date`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `entry`
#


# --------------------------------------------------------

#
# Table structure for table `thread`
#

DROP TABLE IF EXISTS `thread`;
CREATE TABLE `thread` (
`thread_id` int(3) NOT NULL auto_increment,
`user_id` int(3) NOT NULL default '0',
`entry_id` int(3) NOT NULL default '0',
`thread_title` varchar(50) NOT NULL default '',
PRIMARY KEY (`thread_id`),
KEY `user_id` (`user_id`),
KEY `entry_id` (`entry_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `thread`
#


# --------------------------------------------------------

#
# Table structure for table `users`
#

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_id` int(3) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
PRIMARY KEY (`user_id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

#
# Dumping data for table `users`
#

[rhunter007]

Thanks for both of the previous replies!

One thing I don't understand, is why would the "thread" table contain a column for entryid? I may have explained it poorly, but remember that each thread has potentially many entries.

Thanks again.

[YourPHPPro]

Originally Posted by rhunter007

why would the "thread" table contain a column for entryid? I may have explained it poorly, but remember that each thread has potentially many entries.

Oops, you are right. The entries relate to the threads, not the other way around. Remove:

`entry_id` int(3) NOT NULL default '0',

from the thread table.

[hyjacked]

yea that does make more sense, not sure what I was thinking at the time.

Nice catch

ps. that was just to keep you on your toes...haha