Modelling and the element of time

I'm modeling a general business system (party, product, order, shipment, invoice, payment etc.) that will be sold as a product, but parts of it will also be used in other products/projects my company sells. So, I try to solve some general and known problems that can be used by anybody in my company.

The system will contain two parts: a production system (mainly for data entry) and a data warehousing/data mart system (only for reporting and analysis).

I would like to hear some opinions or get some references about modeling the element of time. That problem is solved very nice in the DW concept. But I have also a need for it in the production system.

There is also another problem - that is connected with the time problem - Should I model the complexity of the real world ??!?

Namely, almost always a 1:N relation turns to an M:N relation, when I think about time and/or about the relationships that exist in 10 - 20% of entity occurrences.

For example, the classic relation EMPLOYEE <--> DEPARTMENT is a M:N relation *over time*. Rarely the relation is M:N also *in time*. I resolve the relation with an intersection entity with two attributes (the time attributes):

• From date o Thru date

An example: PARTY and ADDRESS

Some solutions leading to M:N.

PARTY --- < ADDRESS One party has many addresses

PARTY >--- ADDRESS At one address there are many parties

PARTY >---< ADDRESS Well that's it... M:N

I resolve the M:N relationship with an intersection entity

PART -------< PARTY ADDRESS>---------- ADDRESS

In PARTY ADDRESS I can put the time attributes. Notice that the time attributes came as a afterthought.

But the (party) address can have many purposes (billing, shipping, ...) and one purpose can be connected to many (party) addresses. The purpose can also change over time !!!

So we have:

PART -------< PARTY ADDRESS>---------- ADDRESS                              

and

PARTY ADDRESS ------ < PARTY ADDRESS PURPOSE >---- PURPOSE TYPE

In the intersection entity I put the time attributes also.

So what will my user say about such an interface ( a lot of dates) ?. What will the developer say when printing the "bill-to address" on the invoice issued on 08.11.1999 ?!?

Yes, I have modeled the business situation, but at what cost. What is your opinion about complex modeling ?

So back to the history problem. I need the history information for two reasons:

1. Past because the system must be able to show a document as it was when it was printed out. In example, on the contract 123/99 must be the values of the attributes and/or foreign keys (all the way in the referencing line) as there were 1999 (for example: name/address/State/City/ZIP/Org unit/telephone number/ tax percent ... ). Maybe all these things have changed in 2001.
2. Future there must be a way to enter values that will be active in the future. For example, the operator must be able to insert an new accounting schema, a new organization schema, a new tax percent that is known to change in a month from now. Then I can distribute the code table to the other systems. And in a month time, everything will be OK.

Sometimes, the user will enter a document that was issued a month before and the document must have the values of the reference entities that were active a month ago and not the current ones.

So, that are the needs ... But how to solve them in the production system ?!?!?

I think that because of the reason b) my code tables *must* have the time attributes. The reason a) could be solved if I store *not* the foreign keys, but the values of *all* attributes in the referenced entities when the document is "frozen". That means that I must have two entities/tables - one for data entry and another for searching. The data entry entity will have the usual foreign keys, and the archive entity/table will have no FK an all. Just values. All SELECT statements will act on the archive table. After a year, two, etc , I can delete the old documents. What do you think ?

But, because of reason b) I must have the time attributes in the code tables. That means, that the above proposed solution for reason a) is - conceptually - not needed because I could always go for the values via the reference tables + PointInTime. In that way I have complexity in finding the value, but I don't need to have two set of entities/tables. When deleting the old documents, I also delete the "old" occurrences in the code entities.

I don't know what to do :-( I don't have many developers, so I cannot effort to design an ultra-complex system. My customers have - as usual - the need to solve their ultra-complex problems, but they will not understand and/or approve the complex models which will be presented to them. Or will they ?!?!

Sorry, for the ultra-long-and complex post. Hope some will reply ?

Greetings from Croatia,
Drago Ganic Received on Tue Sep 25 2001 - 07:35:45 CEST