Page 1 of 4 123 ... LastLast
Results 1 to 15 of 46
  1. #1
    Join Date
    Jan 2010
    Posts
    19

    Question Separate DBs for individual accounts?

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Anna-J View Post
    The question is, should I actually create a new database for each account or should I let all accounts share the same database
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump View Post
    I don't know about implementation issues for those RDBMSs, but I would always put these in a single DB.
    Quote Originally Posted by pootle flump View Post
    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.
    Eh? I don't see any reason not to use surrogates in this scenario.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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?
    Last edited by pootle flump; 01-11-10 at 11:42.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    EDIT - also, would you mind just outlining a scenario where you do see a reason not to use surrogates?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by pootle flump View Post
    also, would you mind just outlining a scenario where you do see a reason not to use surrogates?
    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.

    Code:
    CREATE TABLE Doc
    (DocID INT NOT NULL PRIMARY KEY,
     VendorNum INT NOT NULL REFERENCES Vendor (VendorNum),
     VendorDocNum VARCHAR(10) NOT NULL,
     UNIQUE (VendorNum, VendorDocNum));
     
    CREATE TABLE Payment
    (PayID INT NOT NULL PRIMARY KEY,
     VendorNum INT NOT NULL REFERENCES Vendor (VendorNum),
     VendorPayNum VARCHAR(10) NOT NULL,
     PaymentDate DATE NOT NULL,
     PaymentAmount NUMERIC(10,2) NOT NULL,
     UNIQUE (VendorNum, VendorPayNum));
    
    CREATE TABLE DocPayment
    (PaymentDocNum INT NOT NULL PRIMARY KEY,
     VendorNum INT NOT NULL,
     VendorDocNum VARCHAR(10) NOT NULL,
     VendorPayNum VARCHAR(10) NOT NULL,
     FOREIGN KEY (VendorNum, VendorDocNum) REFERENCES Doc (VendorNum, VendorDocNum),
     FOREIGN KEY (VendorNum, VendorPayNum) REFERENCES Payment (VendorNum, VendorPayNum),
     );
    Last edited by dportas; 01-11-10 at 17:39.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2010
    Posts
    19
    Quote Originally Posted by r937 View Post
    you honestly prefer the surrogate key of my previous post, as compared with the natural key of this post?
    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?

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Anna-J View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Anna-J View Post
    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?
    This in itself is a very minor issue, and not enough of a reason on its own to prefer surrogate keys to natural keys.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937 View Post
    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

    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •