I'm designing a database for a web small project, where each user creates an account and gets their own set of data. So, from the users' perspective, each account will appear to have its' own database.

The question is, should I actually create a new database for each account or should I let all accounts share the same database, with the addition of an account id in every row?

Assuming the accounts are racing teams and there's a "Driver" table, it would consist of
(DriverID, DriverName, LicenseNo, MiscInfo)
tuples in an account specific DB, whereas if would consist of
(AccountID, DriverID, DriverName, LicenseNo, MiscInfo)
tuples if the DB was shared.

In the former case, LicenseNo would be unique. In the latter case, it wouldn't, since the same driver might race for more then one team and each team should have its' own driver record.

The accounts will be isolated and data should never be shared between them, so from a conceptual point of view, they have separate databases. The question is, should they also have separate databases in the DBMS or would that be an amateurish way of doing it?

Also, I'm not sure how the security would work in DB2 Express-C and PostgreSQL (my target DBMSes) when you want to restrict access to individual records (based on AccountID) in a shared DB solution.

I don't know about implementation issues for those RDBMSs, but I would always put these in a single DB.
This, also, is a prime example of where I favour natural, composite, clustered keys (led by the AccountID column) as opposed to single column surrogates.

pootle flump ur codings are working excelent.

substitute the word "table" for the word "database" (i am surprised how many people seem to confuse these two concepts) and you should still use only one

Eh? I don't see any reason not to use surrogates in this scenario.

Yeah, I know. It is the same argument we have every time and I always say I will put together scripts to illustrate my position.

Tell you what - I'll put together some scripts to illustrate my position!

EDIT - also, would you mind just outlining a scenario where you do see a reason not to use surrogates?

pootle flump ur codings are working excelent.

I have no problem using surrogates all the time.
Neither do I have a problem with not using surrogates on simple schemas, but your statement that the scenario presented by the OP illustrated an example of when surrogate keys would be inappropriate left me nonplussed.

me, me, pick me!!!


CREATE TABLE top_level_domains
( tld_id INTEGER NOT NULL PRIMARY KEY
, tld VARCHAR(9) NOT NULL
);
INSERT INTO top_level_domains VALUES
( 1 , 'co.uk' )
,( 2 , 'com' )
,( 3 , 'ca' )
;

CREATE TABLE emails
( addressee VARCHAR(99) NOT NULL
, domain VARCHAR(99) NOT NULL
, tld_id INTEGER NOT NULL
, PRIMARY KEY ( tld_id, domain, addressee )
, FOREIGN KEY ( tld_id ) REFERENCES top_level_domains ( tld_id )
);
INSERT INTO emails VALUES
( 'pootle','plushtoys',1 ) -- pootle@plushtoys.co.uk
,( 'blindman','yanks',2 ) -- blindman@yanks.com
,( 'r937','canucks',3 ) -- r937@canucks.ca

That may be an example of when surrogate keys are not necessary, but it is not an example of when they should NOT be used, which is what pootle asked for.

And for the record, I'd use surrogate keys for your top_level_domains table anyway. There is no such thing as a lookup table.

blindman, i think you're secretly chuckling at how you're messing with us

CREATE TABLE top_level_domains
( tld VARCHAR(9) NOT NULL
);
INSERT INTO top_level_domains VALUES
( 'co.uk' )
,( 'com' )
,( 'ca' )
;

CREATE TABLE emails
( addressee VARCHAR(99) NOT NULL
, domain VARCHAR(99) NOT NULL
, tld INTEGER NOT NULL
, PRIMARY KEY ( tld, domain, addressee )
, FOREIGN KEY ( tld ) REFERENCES top_level_domains ( tld )
);
INSERT INTO emails VALUES
( 'pootle','plushtoys','co.uk' ) -- pootle@plushtoys.co.uk
,( 'blindman','yanks','com' ) -- blindman@yanks.com
,( 'r937','canucks','ca' ) -- r937@canucks.ca


you honestly prefer the surrogate key of my previous post, as compared with the natural key of this post?

if so, you're an idiot, and i say that in the nicest way possible

Here's one. In the following example DocID and PayID are surrogates. In the DocPayment table however I haven't used them. I've referenced the business key attributes instead. That's because I need to enforce the constraint that a payment to a vendor must only pay documents from that same vendor.

If I used the surrogate key for the foreign keys then things would be more tricky. Most SQL DBMSs don't have good support for multi-table constraints. I'd probably have to write some extra procedural code to check the aforementioned rule (in a trigger for example). To my mind that's a significant step backwards when there is an easy declarative DDL way to do it.

Alternatively I could denormalise. I could use the surrogate keys in DocPayment and put the VendorNum in there as well (in violation of 3NF). To enforce the constraint I'd still need to use a compound key for the foreign key reference however. I wouldn't be using the surrogate in the normal manner as a substitute for some business key and so I don't see a clear advantage to using a surrogate at all. Also I would come up against the silly limitation in SQL that says a "FOREIGN KEY" constraint must only reference exactly the set of columns in a UNIQUE or PRIMARY KEY constraint. So I'd need to put redundant UNIQUE constraints on the Doc and Payment table to make the foreign key constraint possible.

In this example I think the logical disadvantages of using surrogates outweigh the advantages (setting aside the more familiar, practical disadvantages of surrogates: that they require extra space in indexes, more joins etc).

This also nicely illustrates another point: one reason why I prefer to deprecate the term "primary key". If you added other tables that didn't require the same vendor constraint then their foreign keys might very well benefit from using a surrogate. That seems perfectly reasonable to me: use a surrogate where it gives some advantage and avoid it where it doesn't. There's no fundamental reason to limit yourself to "Always use key X in table T". SQL's dubious notion of a "PRIMARY KEY" is therefore of very little use or importance.

Sorry peeps - my question was directly targeted at blindman, not a general question regarding when not to use surrogates. Blindman said "I don't see any reason not to use surrogates in this scenario." To my knowledge, blindman has never indicated any scenario he would not use surrogates.

pootle flump ur codings are working excelent.

Doesn't a surrogate key save a lot of work for the RDBMS, in cases where updating the natural key would cause cascade updates of a lot of dependent rows? Or is this performance issue negligible in practical use, given that you rarely udate a primary key?

um, that's a pretty generic and vague question, and certainly does not apply to the "top level domain" example

in general, the cascade was invented for a reason

This in itself is a very minor issue, and not enough of a reason on its own to prefer surrogate keys to natural keys.

You can call me an idiot if you like, but you're the one who declared your primary key as varchar(9) and is then trying to define the foreign key as an integer....
....and I point that out in the nicest way possible.