One of the pillars of SaaS (Software as a Service) - or software + service as Microsoft like to call it - is single instance mulitple occupancy. This is an overly complicated way of saying "there's only one instance of the code and eveyone uses it". It has to be like that because if you have 1M users and you make a code update, you don't want to upgrade 1M instances right?
Now that throws up a couple of problems. Firstly, if everyone's using the same instance, how do you partition your data? Well, that's an easy one to solve to be honest, you just partition your data by user. By way of example, let's create data for a Customer and partition it by User, like so.
CREATE TABLE [dbo].[CustomerBase](
[CustomerId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[FirstName] [nvarchar](50) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[EmailAddress] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED (
[CustomerId] ASC )WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]
CREATE TABLE [dbo].[Users](
[UserId] [int] IDENTITY(1,1) NOT NULL,
[Login] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED (
[UserId] ASC )WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]
and you'd retrieve the CustomerBase information like so
SELECT
c.FirstName,
c.LastName,
c.EmailAddress
FROM
CustomerBase c
INNER JOIN Users u ON u.UserId = c.UserId
WHERE
c.UserId = 1 -- or whatever User you need.
The second problem is a little more thorny unfortunately. The second problem is that no two users are going to describe their Customers in the same way. Now you could take the old shrinkwrapped software approach to this problem and say "we describe customers like this and if you want to use our product your customers have to look this this", but this is the 21st centuary and that's not very friendly.
So to overcome this problem, we use a metadata solution. We create a table called CustomerBase, into which we put the minimum amout of data that we require to make our software work. We then allow each User to extend the description of a Customer to suit themselves. Now each User has a unique description of their Customer, but we're still using a single instance of the codebase. Here's an example of what I'm talking about.
Create a table to hold the User's extended description
CREATE TABLE [dbo].[CustomerExt](
[CustomerExtId] [int] IDENTITY(1,1) NOT NULL,
[UserId] [int] NOT NULL,
[FieldName] [nvarchar](50) NOT NULL,
[Type] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_CustomerExt] PRIMARY KEY CLUSTERED (
[CustomerExtId] ASC )WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]
Then create a table to hold the value of that extended description and link it back to the original CustmerBase entry.
CREATE TABLE [dbo].[CustomerExtValue](
[CustomerExtValueId] [int] IDENTITY(1,1) NOT NULL,
[CustomerExtId] [int] NOT NULL,
[CustomerBaseId] [int] NOT NULL,
[value] [nvarchar](256) NOT NULL,
CONSTRAINT [PK_CustomerExtValue] PRIMARY KEY CLUSTERED (
[CustomerExtValueId] ASC )WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY] ) ON [PRIMARY]
Now you can get the default values for each Customer, and all the extended values, by User, using the following sql
SELECT
c.FirstName,
c.LastName,
c.EmailAddress,
e.FieldName,
e.Type,
v.Value
FROM
CustomerBase c
INNER JOIN Users u ON u.UserId = c.UserId
INNER JOIN CustomerExt e ON e.UserId = u.userId
INNER JOIN CustomerExtValue v ON v.CustomerBaseId = c.CustomerBaseId
WHERE
c.UserId = 1 -- or whatever User you need
Now, you can either do the casting of the values to their correct type in the database API layer, or pass it, as is, to the application layer to sort out, its up to you and will depend on your situation.
UPDATE: As Arnon pointed out in the comments, there are other ways to partition your data. The one I chose is this one, the other two are this and this.