Database design to support multiple clients

If you were building a database with tables Documents, Clients, Users and Firms, where in Firms you would store the companies that use the software how would you design the first three tables to support multiple Firms to store in them? So, in Documents we want to store documents for all the firms, of course, we need to tell them appart somehow, so we would need a column like FirmID. We can also put this in Clients and Users.

Now the next requirement is that each firm can have its own IDs for documents, clients, because obviosuly when we add a new firm, their IDs for whatever they create should start at 1.

  • Len function on Float in SQLServer gives wrong length
  • Validating UPDATE and INSERT statements against an entire table
  • How to insert into a table with just one IDENTITY column
  • How does SQL Server Wildcard Character Range, eg , work with Case-sensitive Collation?
  • Profiling Linked Server
  • isnull vs is null
  • I was thinking something like this but it requires manual construction of all the fields but RowID.

    CREATE TABLE [dbo].[ClientTest](
     [RowID] [int] IDENTITY(1,1) NOT NULL,
     [FirmID] [int] NOT NULL,
     [ClientFirmID] [int] NOT NULL,
     [ClientFirmPrettyID] [varchar](10) NOT NULL,
     CONSTRAINT [PK_ClientTest] PRIMARY KEY CLUSTERED 
    (
     [RowID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    

    RowID will run automatically in this case but it’s useless for us because for everything we do we need to use ClientFirmID and ClientFirmPrettyID. Is there a way to automate the creation of these two ?

  • Create Temp Table with Range of Numbers
  • SQL getting multiple columns into one column
  • How enable all CONSTRAINT?
  • How resolve this Error Loading ModulesMySQL server has gone away for large joomla database website
  • Begin…End blocks in SQL Server: Is this code right?
  • There is insufficient system memory in resource pool 'default' to run this query. on sql
  • 4 Solutions collect form web for “Database design to support multiple clients”

    It is a good practice to use database facade pattern (views, stored procedures). They hide all database internals (structure) from other world. I think, it is another point that is security. Every firm wants to have restricted access to some data on table level, (not row level). The second point is: performance. It is better have many tables than one big one.

    So I think, it is better to leave tables of every firm as they are and create a view for reports (I think):

    CREATE view dbo.Client
        as
        SELECT  ClientId= ClientId, FirmPrettyId = 'first'
        FROM         dbo.FirstCompanyClient 
        UNION ALL 
        SELECT     ClientId = Client_Id, FirmPrettyId = 'second'
        FROM                       dbo.SecondCompanyClient
    

    or

    ADDED:

    alt text http://i34.tinypic.com/2yydonc.jpg

    Use trigger or stored procedure to generate the next id for some firm and scope (‘clients’ or ‘documents’):

    UPDATE dbo.zz_IdGenerator 
        SET
            @nextId = NextId,
            NextId = @nextId + 1
        WHERE FirmId = @firmId and Scope = @scope 
    
        RETURN  @nextId;
    

    UPDATED:

    Insert UPDATE dbo.zz_IdGenerator... into stored procedure, and call it before insert to the document or client tables.

    alt text http://i35.tinypic.com/dcro9g.jpg

    FWIW, an opinion

    • Would recommend that you keep a unique surrogate PK on all tables for uniquess of join (i.e. Keep your RowID’s)
    • As you’ve suggested with your “PrettyId”, the firm-unique “Sequence” of Document, Client etc for each firm won’t be the PK. You’ll need a separate counters pattern or similar for this. You can also enforce a Unique Constraint on (*PrettyId, FirmId) on each table.
    • Although not 4NF form, it might be a good idea for Security and Sanity purposes to Stamp a FirmId Foreign Key on ALL your Firm Specific tables (Rationale : Just about every query issued by your system will probably want to filter by FirmId). This would also give performance benefits since you won’t necessarily need to join back to the first neighbours of Firm in order to do this filtering (and FirmId FK will need to go into all indexes.

    HTH

    The purpose of a primary key is to uniquely identify a row in a table, not to serve as some kind of a business key which may have some meaning.

    If you need custom numbering sequence for each customer, a column (DocumentCustomId) can be added for that.

    In Oracle, DB2, Postgresql a sequence object can be used — in SQL server you have to create a custom sequencer, hence the DocumentSequence table. The access to the table should be through a stored procedure(s) which should implement

    • create_sequence
    • next_vale
    • current_value
    • previous_value

    When inserting a new document, the DocumentId auto-increments, while the DocumentCustomId should be obtained as a next_value from the sequencer object.

    doc_model_v3

    Here are few links to sequence objects in DB2, Oracle, Postgres to help with the concept.

    You need to setup PK – FK relationship appropriately to handle the table data. For example if an Firm has multiple docs (obviously a firm can), then in the Table of DOcuments you can have a FirmID as fk, which is PK of Firm Table. Or the best to learn relationship is mapping. Do one thing, create the required tables independenlty without any relationship between the tables. Say you have 4 then create 4 tables,that will be Users, Documents, Firms, and Clients. Now create one more table that will handle the relationship of any two table. For example.
    Doc and Firm.

    Craete a new table DocFirmMapTable with three columns DocFirmMapId as its PK, DocId, FirmId. Just you need to take care of that while inserting into the two tables you need to make a one more insert in their respective map tables. Also in map table you can keep mapping of two or more than two tables as per you requirement.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.