First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?
RBAC in the Role-Based Access Control
(RBAC) model, access to resources is
based on the role assigned to a user.
In this model, an administrator
assigns a user to a role that has
certain predetermined right and
privileges. Because of the user's
association with the role, the user
can access certain resources and
perform specific tasks. RBAC is also
known as Non-Discretionary Access
Control. The roles assigned to users
are centrally administered.
DAC In the Discretionary Access
Control (DAC) model, access to
resources is based on user's identity.
A user is granted permissions to a
resource by being placed on an access
control list (ACL) associated with
resource. An entry on a resource's ACL
is known as an Access Control Entry
(ACE). When a user (or group) is the
owner of an object in the DAC model,
the user can grant permission to other
users and groups. The DAC model is
based on resource ownership.
see source
1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources.
2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.
Any way I suggest this data model:
CREATE TABLE ElementType
(
Id (PK)
Name
...
)
CREATE TABLE ElementBase
(
Id (PK)
Type (FK to ElementType)
...
)
(one to one relationship)
CREATE TABLE Element_A
(
Id (PK, FK to ElementBase)
...
)
CREATE TABLE Element_B
(
Id (PK, FK to ElementBase)
...
)
1) RBAC (many-to many relationship)
CREATE TABLE ElementType_To_Role_Rights
(
RightId (PK)
RoleId (FK to Role)
ElementTypeId (FK to ElementType)
...
)
2) DAC (many-to many relationship)
CREATE TABLE ElementBase_To_Actor_Rights
(
RightId (PK)
ElementBaseId (FK to ElementBase)
ActorId (FK to Actor)
...
)
CREATE TABLE Actor
(
Id (PK)
Name
)
CREATE TABLE User
(
Id (PK, FK to Actor)
Password
...
)
CREATE TABLE Role
(
Id (PK, FK to Actor)
...
)
I think the best way to approach this would be to have tables like so:
table Customer
ID
Name
...
table CustomerFields
CustomerID
FieldID
FieldName
... (could have things like type, required/optional, size, etc.)
table CustomerOrders
CustomerID
OrderID
OrderNumber
DeliveryDate
... (all your generics and mappable fields)
table CustomerOrderFields
CustomerID
OrderID
FieldID
FieldValue
By having the table CustomerOrders, you satisfy the ability to map common customer order data to common fields (simplifying reporting at the expense of making the import a little more painful since the fields must be mapped), and the CustomerOrderFields gives you the ability to have the custom fields per customer necessary for the un-mappable data.
The custom fields are still reportable, but not as easily as your generics as they'll come to you in multiple rows (instead of multiple columns). There are some ways around all that depending on your report creator (e.g., pivoting the results).
The only other option would be to do something like this (which, personally, I would avoid):
table CustomerFields
CustomerID
Field1Name
Field2Name
Field3Name
...
Field99Name
Table CustomerOrders
(all your generics)
Field1Value
Field2Value
...
Field99Value
This has the advantage that all your data is in one row, but also has the disadvantage that it isn't immediately apparent from the row what each value means. The first method can always be joined to the field list to give a good definition of the data in each custom field. In addition, what happens if some company requires 100 custom fields? In the above example, you'd be making changes to your data structures and code whereas in the first example, you'd never have the issue -- customers could have infinite custom fields.
I've seen it done both ways, and both ways work. Both ways have their downsides and upsides. The first is far more scalable, but harder to get in to columns (instead of rows). Everything's a trade-off.
Hope that helps some!
Best Answer
One obvious disadvantage is that no matter how good your data model is, it better suit your needs for years to come because you will have just set it in concrete.
I try not to even give app developers access to the the underlying data model. There is a "wall of abstraction" between users and the physical data. That wall is build from a wide variety of views. Just about any format that best fits the user's need can be provided. View triggers do the conversion behind the scenes.
Give your customers whatever logical model best suits their requirement in getting their data to you. So you have to convert it to your current actual model. It sure beats not being able to fix flaws or implement new designs in your database.