As in my previous question, apologies for my incompetence.
I'm working on a database that manages time (or more specifically, person time). I'm having a slight problem with rationalizing some of the tables.
I have a table 'timespan' which represents length of time, and is linked to the person in table 'persons' that the length of time applies to. The person could then be spending that time in one of several ways - just to use three as an example, 'on a project', 'on unconfirmed project', or 'unavailable'.
Previously, I was using one single table 'projects', which contained details of each project, and a join table between this and 'timespan'. When a person was 'unavailable', I was entering a record into 'projects', but with null values in the fields. For a unconfirmed project, I entered a record into 'projects' and used a checkbox to determine whether the project was confirmed. These relied on a record 'timespan.timespantype' to differentiate between the three. Obviously, this is an appaling way to do it, but it worked, just about.
Now I'm trying to make separate tables for 'projects', 'prospects' and 'otherevents' ('otherevents' being for periods where somebody is unavailable or in any other situation one may care to add later). I am having a problem linking these tables in a way that allows me to, for example, use a persons.personID to find details of all of the projects on which a person will be working, or even a general schedule for that person.
I attempted to create three different join tables, but it doesn't seem right to me that timespan.timespanID should form part of a composite key, as it isn't a many-to many relationship.
Any ideas?