My question is more of a conceptual than technical one. I plan to use ColdFusion for what I'm doing, but the subject matter doesn't pertain specifically to CF. If there's a better category for this, someone please let me know.
I am planning a web application that will have many related tables in the back-end and will have, hopefully, thousands of members. Each member will obviously have a unique identifier in a "members" table (think of MySpace.com, but not quite as popular).
My question is basically, which practice is considered best?:
PRACTICE 1: Have the application create new tables for each user (resulting in many tables, whose names would include the UserID, with a relatively low number of records per table)
PRACTICE 2: Have it just add user-specific data (distinguished by the use of the user's ID somehow) to the few structural tables (resulting in many, many records in a set number of tables)
There are many more tables, fields and relationships than laid out below. In order to not give away my concept idea I cannot be too specific, but hopefully this will represent the general idea.
Practice 1 - Many small tables, few records each:
A user just creates an account - for example let's say the UserID is 12345. The app creates the following tables and fields:
tbl12345Events (in this scenario, # of records might be in the thousands)
- intVenueID (related to tbl12345Venues.intVenueID)
tbl12345Venues (in this scenario, # of records might be in low hundreds)
- intVenueID (related to tbl12345Events.intEventID)
In this scenario, for each user there are exists an instance of each of the above tables.
Practice 2 - Few tables, many many records:
- intUserID (12345)
- intUserID (related to tblUsers.intUserID)
tblEvents (in this scenario, # of records could be in the millions)
- intUserID (related to tblUsers.intUserID - you get the idea by now)
- intVenueID (related to tblVenues.intVenueID)
tblVenues (in this scenario, # of records could be in the millions)
In this scenario, just these four tables would exist, and they would each contain a ridiculous amount of records
Would somebody be able to tell me possible pros/cons of each practice? For example, do some database programs have limitations on the number of records allowed in a table, or number of tables allowed in a database? Any help would be appreciated. I know this might be a doozey, so if anyone feels that more points are warranted, please say so.