design for tracking dates multiple check in check out

Question

design for tracking dates multiple check in check out

Shane-207239

Mr or Mrs. 500

Points: 523
More actions
September 12, 2007 at 2:14 pm

#374163

I have a table that tracks plan reviews for a project. There are some dates in there that track recieved and returned dates of plans. Based upon the changes that we suggest there will be multiple recieved and returned dates. How would I best keep track of these? I figure I need another table but I am not sure how I would link them?

Shane

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

Can you explain a little more? I am not sure I understand your problem. What is the problem that you may think you have of keeping track of multiple received date and returned date of a project ?

Shane-207239 Mr or Mrs. 500 Points: 523 More actions · Answer 2

for each record that contains a project we will have multiple dates fro both check in and check out. Rather than have multiple columns I would like to have a table of dates but I am wondering the best way to work this to track all the dates weather I have a table with a datein and dateout column or maybe a table that is a dateType and date or maybe a seperate table for each. There isn't always a date out for every date in. The other issue may be that another table will want a datein dateout also.

So a visual might be like this

ProjectA Indate1 / outdate1

projectA Indate2/ null

ProjectB indate1 / null

ProjectC indate1 / outdate1

projectC indate2/outdate2

projectC indate3/

I would like to remove the extra rows from the project table and put them in their own table.

Does this help?

Loner SSC-Insane Points: 21279 More actions · Answer 3

It is just an idea.

Date_Table (dateid INT IDENTITY(1,1) PK,

DateMMDDYYYY DATETIME,

DateYear VARCHAR(4))

Project_table(Project VARCHAR(10),

datetype CHAR(1) constraint 'I' or 'O',

dateid FK to Date_table)

The Date_table you can put in all kind of different date format, so it can be used by other tables.

my 2 cents

Antares686 SSC Guru Points: 125444 More actions · Answer 4

All depends. I would think you would be best with a checkout process with 2 dates.

Project OutDate InDate

This way you can place constraints on the system to deal with business rules around checkout. Can multiple people check out at the same time or does it have to be checked in befor it can be checked out? In the above design the later can be constrained by a UNIQUE CONSTRAINT over Project, OutDate and InDate. Thus if checked out but not returned you cannot enter a new check out date becuase 2 nulls would cause a collision with the constraint.

Also, surely you will want to report on items beind checked out and for how long. It is far easier to work from the Outdate, Indate version than having to pivot from another way.

If your concern is normalization then I have to say you are going to far. It is normalized. Would only be an issue if you had something like

Project Out1 In1 Out2 In2 Out3 In3

Because you break the 1NF in most cases (there can be exceptions where it is a know limit that this would be right).

You gain nothing from the other way except wasting space (Project value will cost 2 rows per record, plus you have to add another couln to assure you can identify each checkout as seperate especially if multiple checkouts can occurr) and causing yourself headaches trying to deal with mutliple checkouts if not allowed.