Hi all

I'm working on a decision support system. Briefly, it is compromised from two modules: 1) a data entry module (which is a desktop application) that operators use it to enter a bunch of paper based forms about schools to be stored in a backend database and 2) a reporting module (which is Web based) that managers use it to perform different queries over the data and use it for taking future decisions.

The problem is that our customer thinks that the form is possibly not general enough. For example there's a section in the form about the heating system that a school may use and it contains several checkboxes each indicating a differnet kind of heating system. But it's possible that some kinds of heating systems are missing in the form. The customer wants to be able to add these possible missing items to the form without modifying source code.

The current implementation (which I consider a bad implementation) uses the following approach:

There's a table named, say, ITEM and another table named SUBITEM that have the following structure:

ITEM(id int, title varchar(100))
SUBITEM(item_id int, id int, title varchar(100))

A third table is used to establish a relationship between a school and SUBITEMs:

SCHOOL_ITEMS(subitem_id int, school_id int)

Now, for example, the following data means that the school "School X" does have two heating systems "Heating System A" and "Heating System C" (suppose that "School X"'s primary key (id) number is 100):

ITEM(1, "Heating System");
SUBITEM(1, 1, "Heating System A")
SUBITEM(1, 2, "Heating System B")
SUBITEM(1, 3, "Heating System C")
SCHOOL_ITEMS(1, 100)
SCHOOL_ITEMS(3, 100)

Now an administrator can easily add a new kind of heating system to the database and in future one can state that a certain school uses that kind of heating system.

This was not the whole problem. But even having a proven pattern for such a situation can help a lot.

I consider this bad because now I have to use "magic numbers" in my queries. For example to see which schools have heating system the query would lool like:

select s.* from school s, subitem si where (s.id = si.school_id and si.item_id = 1)

One has to know that the 1 is the primary key for the heating system to understand what this query is trying to accomplish. However if I had a separate table for heating system I could have a better and more intuitive query like:

select * from school s where s.id in (select h.school_id from heating_system)

So, if you know a better way to handle this situation I'll be very thankful if you share your knowledge with the me. In fact, is it good to have such a feature or it's better to convince the customer that it's better to update the form structure programmatically?

Thanks in advance,
Behrang Saeedzadeh.

If I could make the title unique (via a constraint), then I'd display/use the title instead of the id. If I couldn't ensure that the title was unique, I'd display the title and the id, then allow the user to pick from the list and have my code reference the id.

This solves the "magic number" problem, as well as making the system more "user friendly" too!

-PatP

There seem to be several problems you are talking about.

Let's take the simplest one first.

If you are trying to replace this query:
with this query:
,

the obvious solution is to create a view called "heating system" that is based on school_items and subitem.


Another problem that you are concerned about seems to relate to the ability of end-users to add, for example, "Heating System D".

That can be done in several ways. One way is to provide a button that lets the user add the information, and then the application does the required work behind the scene. In this case, it would add a record to the subitem table and one more to the school_items table.

I am not convinced that the item and subitem tables are designed properly. The school_items table seems to suggest that the subitem_id must be unique in subitem table. Yet there is no such constraint that I can see.

Ravi

Thanks for all the replies.

Ravi, you're right. I've not showed every detail about the tables in my post. I'll write more details about the problem in the next few days.

BTW - Have anyone faced such a requirement anytime before? If so, could you please tell us what solution you've came up with and its pros and cons?

Regards,
Behrang S.