Integrity question

[ByNary010101]

Ok, so I've got a client that wants to be able to dynamically create and define table structures on the fly from an ASP interface. Once the table structure is done he wants to have the ability to go back in and change the field names as well as remove existing and add new columns. I told him that since the system will be servicing multiple people at one time, messing with an already defined structure could kill the system; am I completely wrong?? If you were me what other options would you consider??

Background on the project (if you need it):
The client is a health care provider who needs a robust inventory tracking system. They want the ability to do the following:
Managers can create new Work Orders and assign them to the various Support personnel. Each type of install has different criteria (as well as criteria that is common among all installs like Site, Dept, Floor, Room, etc) that is required to be filled in, and currently they have 4 types of installs defined:
Desktops and Laptops -
ECN (Equipment Control Number)
MulitUserPassword
UserId

Scanners -
ECN of Scanner
ECN of Attached PC
Make/Model

Printers -
ECN of Printer
ECN of Attached PC
IP Address
Subnet Mask
Domain
Unix Queue
Number of Trays

They want the ability to add new install types down the road and define the criteria that is required to be filled in. To solve this I am contemplating using a Wizard type interface that will guide the Manager through the Install Type creation, and after all of the validation and formatting the end result will be a new Table being created for each Install Type.

 

[SQLSister]

Absolutely do not let them change the existing structure. Don't let them mess with the existing fields or things will break.

Now you need to think how you can design this mess for maximum flexibility.

You clearly need a work order table for the fields which are standard to all work orders (date, location, etc.)

Then you need lookup tables
Type of install

Install Criteria (Three fields, CriteriaID, Type and Criteria)

Then you need a table to relate to the Work Order table.
In it will be the details of a particular type of install.
Fields
Criteria ID
Work Order Id
CriteriaInformation

On the form, the user will select the type of install and then the form will change to dynamically show the required fields for that type. The user will type the information in the field which will be stored in the work order details table with the criteriaID based on which criteris the information belonged to and the info isteself in the Criteria information field.

THen you can have a form where the managers can add new types of installs and/or new criteria and they go to the lookup table which is used to populate the work order form dynamically. ANd never is a structure chagne to the db required. SO they can add to their hearts content and everything will still work, forms, reports, triggers, stored procedures, etc.



Questions about posting. See faq183-874

 

[ByNary010101]

Thanks for that, I think I was starting to head that direction with this. This is what I have:

Table: Walkthrough (a.k.a Work Order)
Walk_Id (PK)
Walk_InstallId (FK Relates: Install)
Walk_NER (New, Existing, Replacement)
Walk_DeptId (FK Relates: Department
Walk_Loc (Location information)
Walk_Room (Room in the Clinic/Hospital)
Walk_Floor (Floor in the Clinic/Hospital)
Walk_Other (Misc. information)
Walk_Instructions (Instructions for the Technician)

Table: Install (Different install types like Desktop, Laptop, Printer, Scanner, etc)
Install_Id (PK)
Install_Desc (Description of what type of install this is)
Install_Status (Flag used to mark an install type as Current or Past)

Table: Field (Used to store the criteria for each Install)
Field_Id (PK)
Field_Desc (Description of the criteria; IP Address, Subnet, etc.)
Field_HtmlId (FK Relates: Html_Element; used to hold what type of HTML element the criteria should be; Text, Radio, Select, etc.)
Field_InstallId (FK Relates: Install)

Table WIF_Values (Bridge Entity between Walkthrough, Install, and Field tables)
WIF_Id (PK)
WIF_WalkId (FK Relates: Walkthrough)
WIF_InstallId (FK Relates: Install)
WIF_FieldId (FK Relates: Field)
WIF_Value (Holds the input of the user for this Field for this Install type, for this Walkthrough)

Table: Html_Element (used to hold the different types of HTML Elements)
Html_Id (PK)
Html_Desc (Will hold Text, Radio, CheckBox, TextArea, Select, etc)

Table: Html_Attribute (used to hold the attributes of the selected HTML Elements)
HA_Id (PK)
HA_Desc (Will hold Name, Id, Value, Cols, Rows, etc)

I may be totally going the wrong way with this but what do you think?? The Manager can pull up a "Create New Install Type" screen, he/she can then define the Install type and then can proceed to define the Criteria unique to this install type as well as how is should be displayed for input (ie Manager inputs as criteria "IP Address" and then will choose how to capture the input from the end user from a dropdown with all of the HTML Elements, "Input Type: Text"). When a new Walkthrough (Work Order) needs to be created, the end user will "Create New Walkthrough"; he/she will then be presented with all of the different Walkthrough Install options; he/she selects the Install type and then is presented with a screen that is populated with all of the criteria required for that install type. Does this sound like it would work??