Database design to support multiple clients

Question

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.

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 ?

Answer 1

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.

Answer 2

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

Answer 3

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:

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.

Answer 4

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.

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