up vote 0 down vote favorite
share [g+] share [fb]

Am in the process of designing a database to a rather large project and well need some feedback from the 'experts' of what way to go.

I have a project with 154 million users that each have something in the area of 9,000 records stored each year.

Option 1: Create a table for each user with the users unique ID, to store their respective data into. Downside is that I then will have a database with more than 154 million tables.

Option 2: Store all data in a single table and then create a column that hold each users unique ID in, when it is needed to retrieve their data. Downside is that more than 1.3 billion rows are added into a single table each year, making it impossible large.

Well simply looking for pro/cons or other thoughts on how to design this, before I am starting out. Am currently leaning towards Option 1 although it do give me some other challenges in matters of viewing the whole DB etc.

link|improve this question
Is the data for each user unique? No duplicates? Freeform? Generally, you would figure out what the possible values were for the various chunks of data, and then have a user table that joined various other tables to construct the needed data set. That way, you aren't needlessly duplicating data. – malcolmpdx Apr 5 '11 at 21:10
feedback

2 Answers

up vote 0 down vote

I understand that this is somewhat off-topic, but nevertheless: is the MSSQL requirement cut in stone? It sounds like a system that would fit a "NoSQL" database well. E.g. mongodb or cassandra. These databases shine when it comes to simple datamodels with few relations and ridiculously large dataset sizes.

link|improve this answer
no, it could be any other type of sequel server - as long as licensing prices would be 'reasonable'. Will take a look at what you suggested. – MikeMike Apr 5 '11 at 23:11
feedback
up vote 0 down vote

Assuming that your data is completely independent (ie User A does not relate to User Z in any way) then you could partition your data in a more-reasonable way by making the tables A-N and M-Z (which could then be managed as separate servers or clusters as load picked up, or further split). Splitting it all the way down to one-table-per-user sounds like something that will show up on The DailyWTF next year, though.

You aren't planning on doing something like (userid,key,value) to get those "9000 records", are you?

link|improve this answer
No, there wouldn't be any kind of relation between user A and B of any kind and since the data is on the rather sensitive level, so it wont be possible to do any comparison of data from one account to another. Actually good idea to break it further up and split the data into multiple databases, already considered to place non-user related tables into a DB on it's own. But you are right, it will be a mess of some sort doing it this way. – MikeMike Apr 5 '11 at 23:25
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.