We help IT Professionals succeed at work.
Get Started

Managing data for many users: many small tables, or few large tables?

ggunnigle
ggunnigle asked
on
227 Views
Last Modified: 2013-12-24
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)

EXAMPLES:
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:

tbl12345UserInfo
 - strName
 - strEmail
 - booIsPremiumUser

tbl12345SavedLists
 - strListName
 - dtmDateCreated

tbl12345Events (in this scenario, # of records might be in the thousands)
 - intEventID
 - dtmEventDate
 - intVenueID (related to tbl12345Venues.intVenueID)
 - strEventName

tbl12345Venues (in this scenario, # of records might be in low hundreds)
 - intVenueID (related to tbl12345Events.intEventID)
 - strVenueName


In this scenario, for each user there are exists an instance of each of the above tables.


Practice 2 - Few tables, many many records:

tblUsers
 - intUserID (12345)
 - strName
 - strEmail
 - booIsPremiumUser

tblSavedLists
 - intListID
 - intUserID (related to tblUsers.intUserID)
 - strListName
 - dtmDateCreated

tblEvents (in this scenario, # of records could be in the millions)
 - intEventID
 - intUserID (related to tblUsers.intUserID - you get the idea by now)
 - dtmEventDate
 - intVenueID (related to tblVenues.intVenueID)
 - strEventName

tblVenues (in this scenario, # of records could be in the millions)
 - intVenueID
 - intUserID
 - strVenueName

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.

Thanks,

Gavin
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE