How would I structure database the best?

Multitenancy: How do I design entities in business layer of a multi-tenant application for an extendable database structure?

• For example, I want that a product may have multi-versions like from different tenants, one version product has 3 fields and other has 6 fields. Then how will I represent this entity in business layer to apply logic of my business on, not in database, of my application. How to konw the semantics of the entities on the fly without a static class. I know how to design the database. I asked about the design in business layer. Where I have business logic for different tenants with different requirements for an entity or many of them. How to handle that when I dont have a specific entity to add methods on? I dont have a product with static properties. How will I add many different discount formulas on an abstract database column that is stored in database only. How to convey different versions of the entity from database to business layer for processing and then to the web client for display and edits. How to represent it between web client and database where it will be processed, the business layer!

• Answer:

  Simple one-to-many relationship.  If some of the fields are common to both types of users, place them in a main table.  For the field extensions you want to make, have a second table that stores name/value pairs (and possibly data type information) linked to the first using the primary key of the main table as a foreign key in the extension table. While not as efficient as compared to explicit columns, it does allow you to add N fields to a linked record at-will.  So it works well as a generic data structure where you want the ability to be flexible in what you store. As an example: MAIN table (Common Fields) ID PRIMARY KEY INTEGER UserName VARCHAR(80) StreetAddr1 VARCHAR(80) BlahType INTEGER ... EXTENDED_DATA table (Extensions) DataID PRIMARY KEY INTEGER MainID FOREIGN KEY to TenantID INTEGER DataFieldName VARCHAR(30) DataFieldValue VARCHAR(4096) Then you would insert your common record info into the MAIN table and get its primary key.  Then insert the user-specific fields into the EXTENDED_DATA table for whatever user type or business data as needed.  Then write your software to configure what optional fields you're going to add to your structure for a given client user.  I've used this simple design for writing general purpose data capture systems such as audit trails or user activity logging.  I simply create a base object type that captures the generic structure and then extend those in my specific application with wrapper and data conversion functions for the extended fields.  It allows you to stand up a new application very quickly.  When we used this technique, it allowed us to "cookie cutter" an entire family of applications.  They all looked different and our users thought they were unique.  But under the hood, they used the same core and was built off a simple template.  We were able to crank out applications in a matter of days down from weeks.