October 8, 2007 at 10:29 am
I am in the process of designing my database for an upcoming project. I have the following situation that I could use some advice on (this example is ficticious. It does however hopefully convey my question).
I have a table called Status that two fields, ID and Description:
1 New
2 Reviewed
3 In Progress
4 Cancelled
5 On Hold
6 Re Submitted
7 Approved
My system is broken down into several business areas. Lets call them:
Area A, Area B, Area C and Area D
Here is my question:
The valid ID values for Business Area A are 1-7.
The valid ID values for Business Area B are 1, 2, 3, 4 and 7.
The valid ID values for Business Area C are 1,2, 3 and 7.
The valid ID values for Business Area D are 1, 4 and 7.
I could add one Y/N Column to the Status Table for each of the Business Areas in order to filter out the unwanted rows, but then I would have to modify the Status table everytime a new Business area required the Status table.
Can anyone suggest a simple yet flexible design that is not a maintenance nightmare?
Thanks
October 8, 2007 at 12:15 pm
I assume BusinessArea and Status in the main table, so why don't you put a CHECK constraint in the Status field based on BusinessArea
Status VARCHAR(1) NOT NULL CHECK ((BusinessArea = 'A' AND Status IN (1,2,3,4, 5,6,7)) OR BusinessArea = 'B' AND Status IN (1, 2, 4, 7)....)
October 8, 2007 at 1:30 pm
Based on your response I have a feeling that my example may have been a little confusing. Here is that same example that have hopefully made a little clearer.
I have a table called Status that two fields, ID and Description:
1 New
2 Reviewed
3 In Progress
4 Cancelled
5 On Hold
6 Re Submitted
7 Approved
The descriptions will be displayed in a drop down List box so that the user can select one of the descriptions. Here is my question:
When the user is on the Customer Maintenance screen I want the ListBox filled with the descriptions with ID's 1-7
When the user is on the Vendor Maintenance screen I want the ListBox filled with the descriptions with ID's 1,2,3 and 4
When the user on the Orders Maintenance screen I want the ListBox filled with the descriptions with ID's 1,4 and 7
I could add 3 columns to the Status table:
CustomerYesNo
VendorYesNo
OrdersYesNo
to help me filter out the unwanted rows but if I do that I will need to add a new YesNo Column everytime I create a new Maintenance screen. I also need to be able to handle the situation when a new Status is added to the Status table.
I guess what I am trying to come up with is a 100% table driven way of accomplishing the task that I have described without constantly changing my Status table or without any hardcoding in the stored procedure that I use to select the rows I want.
Can anyone suggest a simple yet flexible design that is not a maintenance nightmare?
Thanks
October 8, 2007 at 1:46 pm
I'm not sure what you consider a maintenance nightmare, but I will make some suggestions anyway and then see if you think they are maintenance nightmares. These are not in any order based on workability or priority.
Solution 1 - Create a StatusType table (StatusTypeId, Desc) which will hold Customer, Vendor, Order and add a StatusTypeId Column to your table. This would require having multiple records for each status used by multiple status types. For example you would have 3 records with "New" in the description, 1 for each status type. When you create a new maintenance screen you add it to the Status Type Table and then add the appropriate Statuses to the Status table with the new status type.
Solution 2 - Create a StatusType table (StatusTypeId, Desc) which will hold Customer, Vendor, Order and a "glue" table StatusByType(StatusId, StatusTypeId). Then you could create a view using the select statement Select S.StatusId, S.Desc, ST.StatusTypeDesc From Status S Join StatusByType SBT On S.StatusId = SBT.StatusId Join StatusType ST ON SBT.StatusTypeId = ST.StatusTypeId. Here you add the new maintenance screen to the StatusType table and add the necessary statuses and status type to the StatusByType table.
I would probably implement solution 2. Let me know if these are maintenance nightmares.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 8, 2007 at 1:57 pm
Option 2 sounds workable.
I had originally created the following tables:
CustomerStatus
VendorStatus
OrderStatus
Each table only held the rows that were applicable to that table. My boss thought that it was a bad idea to have the same descriptions in multiple tables. He was also concerned because my database has a lot of these so called lookup tables.
I am not sure if his way or my was is better.
Thanks
October 8, 2007 at 2:04 pm
I almost added your original solution to my post as Solution 3, but I decided the downside of having to create a new table anytime you might need to add a maintenance screen that used a status was more maintenance than Solution 2.
Good luck
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2007 at 12:35 pm
Thanks for the advice.
October 9, 2007 at 12:46 pm
without starting a thread war - you'll end up with an entirely different maintenance nightmare - the "update" nightmare. For example - if you wish to change the description for Code 1 ONLY for one of those statuses.
you also have no (decent) ability to enforce data integrity from the DB. (No way to enforce a relationship, no way to make sure that a code that USED to apply doesn't anymore without a LOT of DB code).
An extra maintenance screen to add new codes to distinct tables compared to the heartaches my customers would put me through with a denormalized reference table. Might be easier application-wise - but a nightmare on the DB side.
Besides - you can use just one data entry screen. Have them pick what values they wish to maintain, and dynamically load the correct reference table (they all have statusID and statusdescription, or something close to that).
My .000001.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 1:05 pm
Matt Miller (10/9/2007)
without starting a thread war - you'll end up with an entirely different maintenance nightmare - the "update" nightmare. For example - if you wish to change the description for Code 1 ONLY for one of those statuses.you also have no (decent) ability to enforce data integrity from the DB. (No way to enforce a relationship, no way to make sure that a code that USED to apply doesn't anymore without a LOT of DB code).
An extra maintenance screen to add new codes to distinct tables compared to the heartaches my customers would put me through with a denormalized reference table. Might be easier application-wise - but a nightmare on the DB side.
Besides - you can use just one data entry screen. Have them pick what values they wish to maintain, and dynamically load the correct reference table (they all have statusID and statusdescription, or something close to that).
My .000001.
Matt,
I am not sure what you are proposing for a solution. I think you are saying he should go with his original design (CustomerStatus, VendorStatus, OrderStatus). That is certainly a workable solution and enforces DRI better than my solution 2. But, it appears "the boss" has already vetoed that solution.
I understand what you mean by having a nightmare if you want to change the description for only 1 screen and also when you say you need to write a lot of code to enforce data integrity (make sure only a customer status type gets into the customer table).
I am always ready to hear about a better way to do things. I am not stuck on "I've always done it this way".
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2007 at 1:16 pm
I didn't see veto, more like "was nervous about". Sounded like there was some wiggle room (my mgr would listen if I told him that it was a "fake duplicate").
that being said - if it isn't an option - then option 2 is better than option 1.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 9, 2007 at 2:40 pm
Matt,
You are right, I interpreted it as vetoed and it may not have been.
Could you post some code on how you would dynamically load from the correct status table (customer, vendor, etc..)? I'd be interested in seeing what it would look like. I could write something, but it sounds like you've done it before.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 9, 2007 at 3:00 pm
Actually - it would be in the User interface form.
Assuming .NET 2.0 as a webform:
- form gets 2 visual controls, a drop-down list (with the list of reference tables), and a gridview control. There's also at least a ASP:SQLDataSource object out there.
- gridview control is defined with an empty datasourceID.
- build an event on change of the DDL (meaning - every time you pick a new value). The event would then set the datasourceID again back to "", then update the Insert, delete and select statements in the ASP:SQLDatasource. Then rebind the gridview to the asp:SQLDatasource and force a postback, and voila - gridview has data from the newly picked reference table. Assuming you defined the gridview to allow updates - you have a way to change values in the ref table.
I'd use dynamic SQL in the insert, select and delete statements, since the only input there would be from a DDL (and not keyed in, so as to prevent SQL Injection attacks).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 11, 2007 at 8:28 am
Thanks for the help on this topic (I would have responded earlier, but I am on jury duty so I am in and out of the office). I was wondering one thing. If I did have 'wiggle' room would I be better off with my first option of creating separate tables?
CustomerStatus
VendorStatus
OrderStatus
The only down side that I see (and my boss sees) is that I have more tables (and C# classes) to keep track of. If separate tables are considered the best way of doing this then I think I can convince my boss. Otherwise I will have to go with the two table approach.
Thanks
October 11, 2007 at 9:05 am
My preference it the two-table approach. It means more coding on the SQL Server side, but allows more flexibility in the application. Again that is preference.
If you did do with the separate table approach, couldn't you use a generic Status class with a type Property (Customer, Vendor, Order, etc..) and then load the data into the class based on the Type property setting? I am not an OO or .NET expert, so this may not be the best way to do it.
Please post what you go with and how you think it works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 11, 2007 at 9:41 am
You may not be an OO or .NET expert but you hit on my design very quickly. While I use many status tables in the database, I only use one C# class to process the tables. I purposely mis stated this in the previous email because I thought that it might be confusing to some.
I am going to look into the two table approach. In a past project I had taken over a project that used this approach. Unfortunately the developer made the code near impossible to work with.
One of the issues that I have with this approach is referential integrity. With the multiple table approach I add constraints to the specific table. Using the two table approach I am not sure how I would do this. For instance, given the following tables
Employee
StatusMain
StatusSub
StatusMain has the following fields LookupType (Employee, Vendor...) and Description
StatusSub has the following fields LookupType and Value.
In the Employee Table I store the Value field from the StatusSub. Putting a constraint on the Employee table so that it references the StatusSub table doesn't guarantee referential integrity (since that value in the status could belong to a different lookup type).
Am I doing something wrong?
Thanks
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply