Basic DB Question - Lots of Tables?

Hello,

I'm new at this so please bear with me. I'm creating a MySQL database with a list of users, and associated history events with each user. Suppose there are 300 users, and 100 history events per user.

There are three solutions that I see:

1.
It would seem logical for me to just create a master table for the users, and a table for each user's history events. That means I'm creating 301 tables though. Not only does this fix having to deal with a single table with 30,000 rows, but it also allows faster access since the table is not locked by another user.

2.
I can create the one massive table, with an index for each user's history events.

3.
I blob all of the events into a cell, and then explode them with php.

Which of these solutions is the best, and can you give a little explaination if possible? Thanks a million.

Matt,

If I understand you correctly, you can create 3 tables:

Table: Users
Table: Event History
Table: History Lookup (MV)

The third table, History Lookup, will take care of the multi-values associated with each user; if a user has more than one event in the history, this table to resolve the multi-value issues of a Many-to-Many relationship.

What you would store in the table is the User ID and the associated Event ID.

If you need help, let me know.

Btw: Hello to a fellow Canadian! Where abouts are you from?

Hey hey. I'm in Ottawa

It's a one-to-many relationship I believe...

If I were to use a table for lookups, I envision something like this...

TABLE users:
userid
password
email
eventid1
eventid2
eventid3
etc.

TABLE historyevents:
id
event_description
date

This means I need over 100 columns for the first table?

Thanks for your help.

Okay, I didn't understand correctly! I thought the events were pre-created values... But I see what you mean now.

Still, you're better of creating your tables like so:

TABLE users:
userid
password
email

TABLE historyevents:
eventid
event_description
date

TABLE user-events:
userid
eventid

You don't want to have your users table with columns: event1, event2, event3, etc... because not all users will have the same number of events, and you don't know what the maximum number of events will be. By using 3 tables, you can have a variable number of events for each user.

Hope that helps... Let me know.