BO / DB Design Consideration

BO / DB Design Consideration

Old forum URL: forums.lhotka.net/forums/t/7843.aspx


asp2go posted on Wednesday, October 21, 2009

I am reviewing the redesign of an existing application and looking at best practices and options both for Business Object and database design considerations. I'm looking for thoughts below and will try to explain clearly. There is complete ability to change at this point and the question I would love to hear your opinions (recognizing that there isn't only one way) on is:

If you have a large number (hundreds) of 'user defined codes' (eg. name value lists) used throughout your system how would you handle the design of (1) business objects and (2) the underlying database? Is there a significant CSLA BO benefit one way or the other?

Essentially, the question is do you have an object and/or table for each one of these or combine all of them into a single object and/or table (obviously the object and database design options can also be separated - given CSLA's Name value class I would assume that is the best way to go on the object side?)
As an example of the above, in a Crm application or any large business app you may have substantial numbers of dropdown lists / selectable criteria on many screens whereby the underlying codes/values need to be maintainable by appropriate users. Products may have Stocking Status, Product Category, Manufacturer Group, .... on and on .... again each major entity type can have a huge number of different codes. Some major CRM/ERP solutions (MS CRM /Oracle Erp / SAP...) out there use User Defined Codes to do all of this, effectively a 'list' management scheme that reads against a single major table that has Code Type inside of it to allow the application code maintenance screen to be a common screen for managing these codes just by filtering / authorization set by the Code Type.

To put this into the Project Tracker type example - if you had many different attributes of objects that need to be tracked (i.e. Projects needs to maintain, Status, Type, Group, Financial Code, Team Code, ....  and Resources need to track Resource Type, Location, Primary Region, ..........   etc.) what would you do? Keep in mind that there may be 100s of these and they could effectively all be in the format of:
ID, CodeGroup, Code, Name, Description

Appreciate any thoughts/pros/cons/pitfalls that you can think of.
Thanks!

cds replied on Thursday, October 22, 2009

In my experience, I'd tend to strongly resist doing this from a database perspective:

1. It makes foreign keys much harder to use - you'd have to have foreign key relationships to the single code table, and that makes it much more difficult.

2. Eventually, you might find that somebody wants to add an extra column to a code table which is much easier if they have a separate table each.

However, the abililty to do user-defined "tables" could be a major benefit to some applications. But from a simplicity perspective (particular if combined with code generation) going with separate tables is definitely better.

JoeFallon1 replied on Thursday, October 22, 2009

I have a table in my DB that acts almost exactly as you describe. The only caveat is that we use it for development purposes and the code values are all "known values" so that our app can use them. But this single table manages dozens of various codegroups. We have a single NVL that can be filtered by codegroup. So that 1 BO is used on dozens of screens to provide the code values for that screen and codegroup. It is very useful and simply to maintain. I guess that if users could add more values to the list it wouldn't matter as long as they were pure "data values" and the app did not need to use the newly added values for some logic branching. (How could the app know in advance what values might get added?)

Joe

 

Copyright (c) Marimer LLC