The “proper” way to design a database architecture when entry counts belogning to different users may vary wildly

Architecturedatabase-design

To give a context; we are going rewrite our application in Django framework. The database that is being used by the old system at the moment designed very poorly, without any respect to methodology of DB design, so we decided to re-design the database architecture as well.

One table, that we've been having trouble was something akin to "Items". Different users have different items, and the item amount varies wildly between users, so holding everything in shared tables seems like a bad idea. We thought asssigning private db processes to every single user (like, every user will have their own sqlite process, invisible from others), but I'm skeptical about this idea too as I've never seen or heard something like this in practice. It also feels like we are breaking a layer of abstraction for something we are not sure of.

My experience with such situations are very dim, so every bit of advice-help will be much appreciated.

Best Answer

No, if the items share common characteristics you should put them in the same table and define an index over the UserID.

Related Solutions

Relational database: in-RAM partitioning? Theoretical structure discussion

That's a long question.

First off, my current project (I'm the database guy, there are MMO engine experts to deal with that) is a form of MMORPG based on an off-the-shelf engine. Volumes would be like Eve Online" or "World of Tanks" volumes.

Now for an orthogonal short answer:

separate DB and Engine completely
Don't mix and match because of hardware optimisations
hardware: DB and engine servers will be way different specs
design your database normally

There is a whole lot more of course, but I'd suggest you're over-thinking the problem and shooting yourself in the foot. I'm simply applying the same techniques to my MMO that I used in Investment Banking because IMO most high volume systems should converge to the similar architecture

Same field but different contents

I had to re-read your question a few times to get the sense of what you are trying to do. What I suggest is something I call a class-based multi-multi join. In some db's like PostgreSQL, you can then do some advanced referential integrity checks on the joins, but that's outside my answer.

suppose you have a table:

CREATE TABLE correspondence_class (
     id int not null unique, -- hand-assigned
     label text primary key, -- hand-assigned, don't want duplicate labels
);

Then we can:

INSERT INTO correspondence_class (id, label) 
VALUES ('1', 'person'), (2, 'color'), (3, 'season'), -- and so forth

From there we can then have two more tables:

CREATE TABLE trigram_assoc (
   trigram_id int references trigram(id),
   c_class_id int references correspondence_class(id),
   correspondence text not null,
   PRIMARY KEY (trigram_id, c_class_id)
 );

you would then do the same for hexagrams, lines, etc. In PostgreSQL, you could change your primary key to include correspondence too, and uniquely index only those correspondences attached to some correspondence classes. You could also have deferrable constraint triggers that could be used to ensure that all relevant associations were populated, but how exactly to do this on other dbs is a good question.

Best Answer

Related Solutions

Relational database: in-RAM partitioning? Theoretical structure discussion

Same field but different contents

Related Question