1

I just want to run this model by some of the competent people in here.

The scenario is that I have made an ASP MVC 3 webshop which is going to be used by multiple companies.

One code base, multiple sites.

Ex. I have:

  • company1.acme.com
  • company2.acme.com

And they all should use the same code base, but for users the data in the webshop should be different.

So I made this simple datamodel: enter image description here

In Site I store all the information about the company, ex. Host = company1.

Is it correct to use Host as PK in all the tables?

How could this model be improved?

UPDATE: enter image description here

Martin at Mennt
  • 5,677
  • 13
  • 61
  • 89

2 Answers2

2

Storing host on all the tables is redundant / denormalized if it is not a part of the key.

Take for example Settlement and SettlementLine. SettlementLine only need do know about SettlementID and can get host by joining with the Settlement table.

Host should not be in the PK of the other tables (only FK) at least if ID is an autonumber.

I would have the tables

Site
----
Host (PK)
Name

Item
----
ID Autonumber PK
Host (FK Site.Host)
Name

SettlementLine
--------------
ID Autonumber PK
SettlementID (FK Settlement.ID)
ItemID (FK Item.ID)

Settlement
----------
ID Autonumber PK
Host (FK Site.Host, Profile.Host)
UserName (FK Profile.UserName)

Profile
-------
UserName PK
Host PK (FK Site.Host)
Name
Albin Sunnanbo
  • 46,430
  • 8
  • 69
  • 108
  • Thanks for your answer. The thing im thinking about is that I might want each company to have their own number series for settlements and settlement lines. So that ID 1 can be an ID for both company1 and company2, then I need Host as PK in those tables. But I do not know if this is the best solution. – Martin at Mennt Oct 26 '11 at 18:50
  • @Martin - Is the "ID" column you are proposing a system assigned value like an IDENTITY, or a user assigned value? – StingyJack Oct 26 '11 at 18:58
  • I have not decided yet, the easiest would be to assign it automatically. But also I might want the customers to create their own ID at least on items. Settlements can be an auto assigned ID, but still I want to separate the ID's so that company 1 has settlement ID 1 and company2 has settlement ID 1. – Martin at Mennt Oct 26 '11 at 19:03
  • @Martin - OK, so set a system ID (IDENTITY), use it for relational purposes, and also let them create some user specified value in another column on the row. – StingyJack Oct 26 '11 at 20:31
  • @StingyJack - I was thinking about that, but was not sure if that was best practice or not. Specially since @Catcall keep repeating `ID numbers have nothing to do with normalization.` :P – Martin at Mennt Oct 26 '11 at 20:36
  • 1
    Storing host (the tenant) in every table isn't redundant in a shared everything, multi-tenant database; it's absolutely essential. It's also essential that it be part of the key. (Which is why ID numbers in every table are a bad idea here, and simply renaming them is also a bad idea.) – Mike Sherrill 'Cat Recall' Oct 31 '11 at 17:07
1

I added a "multi-tenant" tag for you.

This SO answer has a concise synopsis of the structural approaches, and a link to an article that has more details.

Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • Thanks! I have pretty much decided to go for the `Shared database, shared schema.` approach. Right now im only looking to verify that my model is good enough. As I have commented above, I want each tennant to have their own number series for at least settlements and items, as I see it my model covers that, but is this best practice? – Martin at Mennt Oct 26 '11 at 20:15
  • I think your coverage of settlement id numbers and item id numbers is ok. You can write functions to populate them automatically. But you need to drop a lot of those columns named "ID". Joins always need to include the tenant identifier ("Host", in your case) to prevent spilling one tenant's data into another tenant's query. – Mike Sherrill 'Cat Recall' Oct 26 '11 at 20:43
  • Not completly sure if I got what u meant by `But you need to drop a lot of those columns named "ID"`. I have updated my model (above), renamed all the ID's. But the model it self is pretty much the same. But I think I will go for this model, if you do not see any big hang ups? – Martin at Mennt Oct 26 '11 at 21:05
  • "Drop" and "rename" are different things. Look at the article in that link. See how their tables are built? No row ID numbers to join on. (Because that would be disastrous.) – Mike Sherrill 'Cat Recall' Oct 27 '11 at 14:52