Start date, End Date and renew dates

BEFOR

I have a table that contains directory-style listings, and where I need to record the start and end dates of the listings (preserving the start date)

I see 2 options when it comes to handling renewals for these listings:

a) either place start & end date columns in the main table, and link out to a joined "renew" table, which would handle only start and end dates for renewals following the first year.

-In this case, could I do a SELECT statement that utilizes a date BETWEEN from the first table OR to a BETWEEN falling on the second linked table -- with only relevant dates coming up. Or...

b) link out to a table from the main table that handles all run time dates, from the first run of the listing, onward.

It seems this total linking out to another table is efficient, but it always requires that another step in the form process takes place! (never seem to get any input on this topic)

What's the best route to take?

Roger_Ramjet

I'm not sure what you mean. If you can explain things a bit more I'll see if I can help.

BEFOR

Option is either to insert a first start and end date, in two main table olcumns. Or link to another table out of this main table that can retain the second start and end date onward. Another alternatice is to have this other talbe contain all start and end dates wihout exception.

If only a single (the first start and end date) are kept in the main table (and not a linked table), when I do an echo for a BETWEEN the two dates, I wondered whether to use an OR that would pick up on the second recorded start & end date in the linked table also (meaning, for a SELECT statement that would combine two BETWEEN dates - (1) from the first table and (1) from the seonc table -- to pick up on the then currnet run time.

In the first instance, I would preserve only the first start & end date in the main table with everyhing else. The reason to do it this way is to eliminate a confirmation page that would Insert the first start and end date alone by themselves in the linked-to table.
This makes registration a little easier for the user, one less step. If they want to renew next year it would take a confirmation page then anyway, and the second year (start & end dates) would be inserted in the linked to table for the first time then)

I'm trying to come up with the most efficient table structure for handling renewal dates that occur on a ryearly basis while at the same time cut down on the steps it takes the user to register with my site. [ some already have more than five pages of forms to fill out --see what I mean? also I know about the tendency for users to "drop off" the more in-depth form registration within a site is]

Roger_Ramjet

Well, my usual solution to this type of problem is to 'break structure' in my design. One could use either method to store the data, as you say, but I actually use both at once.

Where I have attributes that change on a regular basis, eg account/order status and I want to record a history, I keep the current status and the date it was set, etc in the main table and also record it in the history table. This introduces redundancy that is not strictly according to the relational model, I am 'de-normalising' some of the data. What it does do is simplify queries by elliminating a join while still recording a complete history, also readable without a join. It also makes rolling back a transaction much easier, and if the main table becomes corrupted it helps with reconstructing the data.

How you keep the tables in sync depends a lot on the database you are using. If it supports Triggers then that is how one does it. Otherwise, use transactions so that the update and the insert are always co-ordinated.

BEFOR

Roger Ramjet,

Thank you for the thoughtful reply. About breaking with structure -- I see how normalization may not always take absolute priority with my table strategy.

Probably, I can write a trigger to start a row in a seperate date table (inserting after the main form Insert) And since I need to manually review each listing anyway prior to it being posted onsite, I'll do an Update on this created row from within an administrative panel. Having the initial start date in the main table (could always echo this off into this proposed "date table")

I'm faced with a similiar situation with the directory listings themselves.

These three-to-five line descriptions being Inserted are later editable by the registered site-user/company. This I think says that a seperate table for this also is needed, if a record of each prior listing needs to be maintained. (not to mention -- this will enable the "description prior to edit" to stay online before the "edited description" is manually approved.)

Isn't it possible to do an "Replace and Insert" - where the present value is inserted into another table while a new value replaces it? Don't know if I care to fool with this though.

I believe you can take some of these concepts to too far, that they make certain assumptions such as with server performance, uninterrupted states, etc.

Or having too many tables dispersed that require joins. Making Updates a little trickier. Since I can only do an Update from a single table at once. Although this too could probably be done with an after trigger isn't the single step form more dependably accomplished?

Roger_Ramjet

It can be argued that the current state and the history are 2 different entities. Certainly, history once written does not change it only grows, while the currrent state as recorded in the main table is a dynamic entity that does not grow but only changes. Storing these 'entities' in seperate tables is true to form.
The alternative is a trigger that migrates the old state to the history table when an update occurs. This has all of the technical problems of the first solution, just at a different step. It results in far more errors over the life of the db than the first, which is one of the reasons I use the first approach. Believe me, it just will make things a lot simpler in the long run.

BEFOR

I keep the current status and the date it was set, etc in the main table and also record it in the history table."

This I beleive would be the best structure for my application. But how are you doing this? With this structure would you hypothetically be able to echo current values onsite prior to "manually approving" the update/edit?

Would it make sense to have two cols on the same main table for this: a) one to contain the live desciption (already manually approved) and b) one right beside it that contains a "requested edit" by the site user? Then, in the event "b)" is approved --> "a)" is first moved to the history table while "b)" is then moved to the "a)" col slot to replace it.

But this seems like a lot of moving around just for an edit. (maybe a two step process within an administative panel? -- the dates as well would need to be echoed off to track the dates of these edits) I could possibly used triggers but would rather not.

use transactions so that the update and the insert are always co-ordinated.

How so? I appreciate your responses -- I have other sensitive important info (insurance #'s, etc) that I really need to maintain a record of their history. Anything not this critical and they might as well edit the same values at will (phone #'s, etc)

One last question - if two or three col's in a table are editable -- wouldn't it make sense to backup the entire row to a history table (maybe trip a "0" or a "1" for each possible editable field -- then running a statement on a row if ever I need to see which field was edited.

My main concern right now is if these factors impact my main table setup as this could be done afterward if the present structure is adaptable with present form Inserts.

Roger_Ramjet

Well? There are so many ways to do all of this, and so much depends upon the application and your platform, a general answer is difficult.

First thing to code for the project is your error recording. This will be your fall-back if an archive operation fails.

The easiest and most maintainable and quickest trigger to code is one for each table that takes a snapshot of the whole record after any update. Then you don't have to worry about which updates you do need to record. It is sod's-law that down the line you'll find yourself wishing you had recorded some of the stuff you didn't think you'd need.
All triggers should opperate as transactions, that is if the archive query fails then the update is rolled-back and an error condition dealt with by you in your code.
You may not be able to afford the disk space that whole-record snapshots entail. Just record what you need and can handle.

If you don't have triggers then in code procedures you again use transactions to first update the main record then record the new data in the archive. If the archive query fails then the update is rolled back. If either query fails you have an error condition you need to handle.

You will notice, I practice archive after update since I record current state in my archives, some would archive before update and only archive current state just as it is replaced and becomes history. It is very much up to you which you choose.

Either strategy allows you to roll back to a previous state if the user wants to, or you do.

I do it because the archive can be located in a different db on a different disk or server. It always contains the latest state so makes recovery a lot easier if the main db gets corrupted. Keeping the main db small makes it faster in a production environment where user's look-up the data more than they edit or add to it.