Design of Decision support systens

From: Rajaram M Bhakta <raja_at_netcom.com>
Date: Tue, 2 Aug 1994 19:28:53 GMT
Message-ID: <rajaCtxA46.327_at_netcom.com>


Hi,

Recently i was asked to design a decision support application which would be fed by a no of diverse applications. This application had to be generic enough to accept EDI feeds from any application.

The application involves tracking the movement of items from a point A to a point B. A no of events could happen to the item in its movement from point A to B.

The decision support appliction will run off a query-only database. The database will be populated by means of EDI from diff applications.

Performance is the key factor we have been told to look at. I have come up with a highly denormalised design wherein i have based the application around 3 major entities called KEYS, EVENTS & EVENT DETAILS

Every event happens to a key and there will be an event detail for every key/event combination.

The entity KEY consists of all the attributes which the user could query on like Purchase Order No, Invoice No , Item Nos etc.

The entity EVENTS keeps track of the type, date & time of the event.

The entity EVENT DETAILS (resolve of the many-many relationship between

                          KEY/EVENTS)

holds more information about the event such as QTYs, LOCS etc.

In addition to these 3 major entities, I have some more entities which hold information about some of the KEY attributes.

As events occur, the KEYS entity will have more and more specific information. But in order to link up the sequence of events we will have to repeat the keys.
Thus initially we would have a PO with some events. Then we would have a SO which has been created based on this PO and some events and so on.    

     eg  : P1 I1          (PO ISSUED)
           P1 I1 S1       (SO Created for P1)
           P1 I1 S2       (SO Created for P1)
           P1 I1 S1 ---   (Some more events & update to key)

| --
| --
| --

I would like to know whether someone has been faced with a similar situation before. What was the path that you took?

Would such massive denormalisation cause any problems that might impact performance? Space is not a big issue.

Please let me know if you need any more details.

Thanks
-Raja

Email : raja_at_netcom.com Received on Tue Aug 02 1994 - 21:28:53 CEST

Original text of this message