I have a person entity. I need a way to categorize people on a client by client basis without needing to make db changes for each client.

For example client A might categorize people by JobCode and client B might categorize the people by OshaCode. The point is that for each client there is a certain way that the individual client organizes people. I could give a person entity a JobCode attribute and OshaCode attribute, but these fields would only be used for client A and client B.

When I deal with client C and realize that they organize clients by some other means, I am off programming again.

In a nutshell, my application needs to collect facts for measures that are user definable. (The term fact and measure is comming from my knowledge of data mining models)

Does anyone know of a DB architechture that would help me acheive my goal?

Devildog74 wrote:

I have a person entity. I need a way to categorize people on a client by client basis without needing to make db changes for each client.

For example client A might categorize people by JobCode and client B might categorize the people by OshaCode. The point is that for each client there is a certain way that the individual client organizes people. I could give a person entity a JobCode attribute and OshaCode attribute, but these fields would only be used for client A and client B.

When I deal with client C and realize that they organize clients by some other means, I am off programming again.

In a nutshell, my application needs to collect facts for measures that are user definable. (The term fact and measure is comming from my knowledge of data mining models)

Does anyone know of a DB architechture that would help me acheive my goal?

Remember in relational db theory every attribute of a table should depend upon the primary key and only the primary key (that's relatonal theory in a nutshell). So you are absolultely right, OshaCode only applies to certain people, not all, and therefore shouldn't be in the Persons table. Sounds like you should subtype, i.e., create separate tables for the various subcategories of people (very much like subclassing an object in OOP).

Persons table contains ALL people and attributes that all people have, such as ID, name, address, etc.

Then each subcategory is a different table, the PK of this table is still PersonID, but it is of course a FK to Persons table.

This is one possible solution. Perhaps someone else will have another?

I agree with Jim's approach. Later on, if you need any other XCode, you'll only have to create another table for this particular type of person, instead of modifying the unique 'Person' table, which is not always easy to alter.

Uy.

Both of your points are valid and follow a traditional normailization approach to DB design.

I was hoping to be able to "coin" a way to allow users to define their own "XCodes" without getting a developer involved in creating the table, the new relationship, the middle tier code gen, and the UI to display and collect the new "XCode".

For example, I already have a way to allow users to define their own custom fields and the data types. I have also created a way to allow users to define their own pick lists / combo boxes. For example, the user can currently create custom field definitions, associate these definitions to OrganizationEntities, and PeopleEntities. Once the fields have been defined the UI can collect the data and save the data to the database in the appropriate column for the appropriate Organization or Person Entity.

The kicker is that from a business stand point, we make no business decisions or aggregation of the Organizations and People based on the physical values collected in the custom fields.

One approach that I was considering was to allow the users to define a composite key, then create a table based on the properties of a composite key. For example, client A might define a mask for their key as AAA-999 and client B might define their key as AA99-99999. At the time that this key was created, client A's XCode table would look like this: KeyId int identity(1,1), PersonId int (FK to person) KeyField1 char(3), KeyField2 char(3)

and client B's XCode table would look like so: KeyId int identity(1,1), PersonId int (FK to person) KeyField1 char(2), KeyField2 char(2), KeyField3 char(5)

Assuming the key definition was stored in the client config table we could always foward and reverse engineer the values taken from and displayed in the UI using the XCode entity and the key definition.

I was also thinking that I could create multiple XCode tables, i.e. XCode1, XCode2, etc. and allow clients to define multiple XCodes and assign the XCodes to People in any way they wish.

Does this sound like OTT functionality or what? Once again your thoughts are much appreciated.

Devildog74 wrote:

I have a person entity. I need a way to categorize people on a client by client basis without needing to make db changes for each client.

For example client A might categorize people by JobCode and client B might categorize the people by OshaCode. The point is that for each client there is a certain way that the individual client organizes people. I could give a person entity a JobCode attribute and OshaCode attribute, but these fields would only be used for client A and client B.

When I deal with client C and realize that they organize clients by some other means, I am off programming again.

In a nutshell, my application needs to collect facts for measures that are user definable. (The term fact and measure is comming from my knowledge of data mining models)

Does anyone know of a DB architechture that would help me acheive my goal?

Actually, what is done a lot of times is to have all of your sub table fields as part of your primary (person) table. Then, you can subclass the Person entity that LLBLGen creates to handle the person variations. For example

 class Employee : Person 

Might expose a "Wages" property.

 class Customer : Person 

Might expose an "Account" property.

Regardless, the Person table contains both a Wages field and an Account field.

Person Table
PersonID int Identity
FName varchar
LName varchar
...



PersonAttributeValue Table

PersonAttributeValueID int Identity
AttributeValue varchar
---



PersonAttributeName Table

PersonAttributeNameID int Identity
AttributeName varchar
AttributeType varchar



Person-AttributeLink Table

Person-AttributeLinkID int Identity
PersonID int (FK to Person)
PersonAttributeNameID int (FK to PersonAttributeName)
PersonAttributeValueID int (FK to PersonAttributeValue)

You lose any 1:n semantics due to the inherant n:m structure of the design, but this allows the user to create any PersonAttribute they wish (JobCode, OshaCode, etc), assign a value to it, then connect that name/value pair to a person. Your BL will have to enforce 1:n semantics if you want to have them.

You can probably extend the design to include a FK to the Client table (if you have one) from the AttributeName table to create a strong connection between the Client and the custom attribute they picked. Alternatively, just store it in a config file.

Additionally, placing the type in the PersonAttributeName table and ensuring that the string stored can resolve to a proper .NET type allows you to cast the data on the fly for bounds/range/type checking.

The additional benefit is that your Person table isn't cluttered up with irrelevant columns. I think the only thing you lose is strongly-typed data and certain types of referential semantics, but these are easily overcome with a strong middle-tier.

Grouping then works like this:

SELECT a.*, d.AttributeValue FROM Person a
JOIN PersonAttribute-Link b ON b.IDPerson = a.IDPerson
JOIN PersonAttributeName c on b.IDAttributeName = c.IDAttributeName
JOIN PersonAttributeValue d on b.IDAttributeValue = d.IDAttributeValue
WHERE c.AttributeName = 'OshaCode'

Jeff...

Hmm...I guess alternatively you can have the AttributeValue table point directly to the AttributeName...might be better

<Edit> Oops, I hit quote instead of edit...Frans can you delete the earlier, duplicate post? Thanks.

Alexdresko, thanks for your response, but I think that approach is still too static and requires too much coding in the event of changing requirements.

Jeff, I ran some sample data through your tables and it is very flexible. I think your schema is what I am looking for. Thanks for the great suggestion, its a big help.

jeffreygg wrote:

You lose any 1:n semantics due to the inherant n:m structure of the design, but this allows the user to create any PersonAttribute they wish (JobCode, OshaCode, etc), assign a value to it, then connect that name/value pair to a person. Your BL will have to enforce 1:n semantics if you want to have them.

That's a neat way to do it though. I like that better than my suggestion.

Devildog74 wrote:

Jeff, I ran some sample data through your tables and it is very flexible. I think your schema is what I am looking for. Thanks for the great suggestion, its a big help.

Glad to help

Jeff...