There's a lot of info that I need to keep track of by just the year. If I enter it as a normal date, I would need to extract the year every time I need to query and then do what ever. Would it be easier just to extract it once, convert to an integer and use it like that through out the system when I need to query by year?

Thanks,

scratch

Extracting year from a date and storing it separately violates second normal form. I would only do it if I needed to index the year field. Otherwise use the Year() function in your queries to extract it.

the year was going to be pk in a one field table and then fk elsewhere. Not sure if that makes a difference. One thing that I didn't realize at the time is that I wanted to seperate the date from dd mm and store the dd mm as a dd mm. Now I'm guessing that the dd mm can only be stored as a string or it will default to the current year.

How about this:

There are The

Placement Number + Start Date + End Date (fk + pk + pk)

or

Placement Number + Start Date, End Date (fk + pk )

If I only index the Start Date and End Date, I'm left with the Placement Number as the pk (which is actually an fk in another table)which will repeat often since there are currently only 2 types of placements. Suggestions? If you need more details, just ask.

scratch



scratch

I would only do it if I needed to index the year field.

Do you mean stripping it out and storing it in the same table as the dates it was stripped from as an indexed field? If not, could please give an example of what you are referring to?

scratch

I don't see any business rules regarding uniqueness described in your post. Is there some reason why you don't just use an autonumber primary key?

Dates are not stored as strings. They are stored as serial numbers so the value for July 13, 2005 is actually 38546. This makes doing arithmetic with dates a piece of cake but working with its parts requires using functions.

I could use an autonumber and that would work. I would have to use more complex queries to refer back to the schedule table (Placement Number + Start Date + End Date ). The business rules are this: there currently 2 types of placements for students but possibly more coming. Each year there are 2 placements (one of each). Each year has a unquie start date and end date (that's why I was considering it being part of the key)that is used. Most of the students complete their placements normally (the same start date and end date). That's why I'm considering using a format like Placement Number + Start Date + End Date. Each placement that has ever happen or will happen has a unique start date and end date. If I were to use an autonumber, I can index the the start and end dates to make sure they are unique. The only concern I have is that it is parent table with many child tables. If I was to query it, I suspect (but don't know for sure) that I would slow things down since it referenced so far back.

PS I wanted to make sure that the Start Date and End Date never repeat so it has to be indexed or a pk.

scratch