Should dates for any temporal database saved in a single or 2 tables ? If a person does not this violate normalisation ?


DATE1 and DATE2 Posts indicate that INFO1 and INFO2 are true for that period between DATE1 and DATE2. If DATE < TODAY, the details are deprecated and should not show anymore within the interface however they should not be erased for historic purpose. For instance INFO11 and INFO21 are actually deprecated.

Must I split this table ? Must I keep condition (deprecated or current) within the table ?

To explain the question additionally, Deprecated may be the expression used through the Business, should you prefer "not current", the issue is not semantic, it isn't about sql queries either, Among the finest to understand which design violates or most closely fits Normalisation rules (I understand normalisation isn't necessarily what you want, that's not my question either).

Normalization is really a Relational database concept - it doesn't apply too to temporal databases. That's not saying that you simply cannot store temporal data inside a relational database. You certainly can.

But when you're going with Temporal Database Design, then your concepts of Temporal Normalization apply instead of Relational normalization.

"I wish to know which design violates Normalisation rules"

Is dependent which group of normalisation rules you need to pass.

The foremost and probably breach of normal forms, as well as in Date's book it's a breach of first NF, is the finish-dates within the rows that hold "current" information (making abstraction of the potential of future-dated information) : you violate 1NF if one makes that attribute nullable.

Violations of BCNF may clearly occur as a result of the selection of secrets (because it is the situation in nontemporal database designs too - the temporal aspect does not matter here). Wrt "selection of secrets" : if you are using separate start- and finish-dates (and SQL type of leaves you not one other choice), then probably you need to declare TWO secrets : one which includes the beginning date, and something which includes the finish-date.

Another design problem may be the multiple data posts. This problem is talked about quite in particular in "Temporal Data and also the Relational Model" : if INFO1 and INFO2 can alter individually of each other, it may be easier to decompose your tables to carry only one attribute, to be able to avoid an "explosion of rows count" that may otherwise occur if you need to produce a new complete row each time a single attribute within the row changes. For the reason that situation, your design while you gave it comprises a breach of SIXTH normal form, as (that normal form is) defined in "Temporal Data and also the Relational Model".

You haven't indicated this is from the dates. Will they make reference to (a) the time once the mentioned fact was true in tangible-existence, or (b) towards the period once the mentioned fact was thought to be real through the holder from the database ? If (b), i quickly would not get it done by doing this. Slowly move the up-to-date line for an archive table/log immediately once the update is performed. If (a), then your following statement is questionable :

"the details are deprecated and should not show anymore within the interface"

If your fact does not "have to display in the interface" any longer, it does not have to be within the database any longer either. Keeping such details there accomplishes just one factor : deteriorate general performance for the relaxation.

If you actually need these historic claims of fact to match your needs, then odds are that the so-known as "deprecated details" continue to be greatly highly relevant to the company, and for that reason not "deprecated" whatsoever. Assumming that because of this, you will find hardly any "truly deprecated" details inside your database, your design is nice. Just keep the amount of "truly deprecated details" small by periodically getting rid of them in the operational database.

(PS) To express that the design is nice, does not mean you will not encounter any problems. SQL is very ill-suited additional type of information stylishly. "Temporal Data and also the Relational Model" is a superb management of the topic. Another book, the main one from Snodgrass, is frequently recognized too, though not by me. That certain is one thing of the cook book with quality recipes for coping with these complaints in SQL, as proven through the following conversation on SO relating to this book :

(Q) "Why would I just read that ?" (A) "Since the trigger you requested is on-page 135."