Sensible table structure?

I'm working on an access application, part of which involves assigning personnel to different projects for different amounts of time. So far, so good.

The problem I'm having is that the projects can be either projected (tendered for) or firm. Projects that have been tendered for have different attributes to those that are firm.

The way I've tried to do this is having a Tender table, a Projects table, a Timespan table, and a Persons table.

The Timespan table contains a lookup to Persons.
There are two join tables, one between Timespan and Tender, and one between Timespan and Project.

One problem is that there's nothing to stop a Timespan being joined to both a Tender and a Project, which is not really ideal.

My main problem, though, is that it just doesn't seem logically sensible to me to order it like this. It's making it irritating to put sensible queries together (I'm not particularly experienced or skilled with Access).

If anybody has any suggestions as to how I can arrange things more sensibly, I would be extremely grateful.

Many thanks,

I assume by "tendered" you mean they are slated for future - just not an active project OR it could be yes/no (Tender or Not).

Does your TimeSpan table contain timespans for your organization or timespans for the tender/projects? Can't a project and a tender occur at the same time?

If you think about it a bit - a tender may not have different attributes from projects but only different values assigned to an attribute.

Here are a few tables I have in a project database (it actually has about 40 tables). Each project can be a different size and type. Each can have it's own Milestones and Personnel assigned. The company I did this for has set release dates for projects depending on project size and complexity. Personnel can have different roles on different projects (could be developer on one and project lead on another)

tblProjects ((pk)ProjID, (fk)ProjTypeID, (fk)StatusID) - projects
tblPersonnel ((pk)PersID) - org personnel
tblProjPers ((fk)ProjID, (fk)PersID, (fk)RoleID) - pers assigned to projects
tblProjTypes ((pk)ProjTypeID) - organization project types
tblMilestones ((pk)MilestoneID)
tblProjMilestones ((fk)ProjID, (fk)MilestoneID)
tblProjStatus ((pk)StatusID)
tblRoles ((pk)RoleID)

I don't really expect an answer unless you have more questions. Just trying to spark some more thought... :D

I would think that much of your data would be duplicated in your Tender/Projects tables; a Tender, after all, will hopefully become a Project, correct? What do you do when it becomes an approved Project? Why not simply have a single table for both, and have a Checkbox, named something like Approved, that is checked if the Tender is accepted? You can easily have forms based on queries that display Tenders, Projects, or both. We both displayed, you can see active Projects with proposed Projects, side by side.

Welcome to TheScripts!

Linq ;0)>

If you haven't already, I'd take a short while to go through Normalisation and Table structures. I think it may help you to get a better grasp of how and why things are better ordered in the ways suggested. I certainly found it helpful.

Many thanks to all three of you.

Does your TimeSpan table contain timespans for your organization or timespans for the tender/projects? Can't a project and a tender occur at the same time?

My timespan table contains timespans for which a person to be assigned to either a project or (provisionally) to a tender - the main function of the application is to keep track of a large number of personnel. Hence, a person can be assigned to many projects (both in the present and the future) and many persons can be assigned to a project. Many different projects and tenders occur simultaneously, which the current structure I have allows for.

If you think about it a bit - a tender may not have different attributes from projects but only different values assigned to an attribute.

and

I would think that much of your data would be duplicated in your Tender/Projects tables; a Tender, after all, will hopefully become a Project, correct? What do you do when it becomes an approved Project? Why not simply have a single table for both, and have a Checkbox, named something like Approved, that is checked if the Tender is accepted?

True to some extent - but there are some different attributes: The Tenders and the Jobs have a different numbering system: Tender number vs Job number (although the tender number is preserved in the Jobs table as well). I can now see I've bodged that a bit.

Problem is, Projects have attributes that a Tender won't have, such as a mobilisation date (cannot be known at tender stage), while a tender has "Tender Deadline" and "Tender to leave office" dates. The two tables will need to have different types of attached files as well. I'm not sure what to do about this.

I may also want to be able to declare a person unavailable for a timespan with a reason (on holiday; working for somebody else, etc) - which complicates things further. I've provisionally created a join table between Timespan and a new table 'Status', but this of course is the same principle as what I have already been doing that has been causing me problems.

If you haven't already, I'd take a short while to go through Normalisation and Table structures. I think it may help you to get a better grasp of how and why things are better ordered in the ways suggested. I certainly found it helpful.

It's very useful - thanks. There's a point made in it as well which illustrates the problem I have:

A relational table is in third normal form (3NF) if it is already in 2NF and every non-key column is non transitively dependent upon its primary key.

In short, I'm finding it hard to determine which of these dependencies are transitive.


Many thanks for all your responses, it's very kind of you.

I think we're all pleased if we can help :)
I would consider a couple of points.

1. Unless the fields in the Project state have to have mutually incompatible statuses from those when in the Tender state, you can still have a table which includes all the possibly required fields. They would simply be unused for the particular state they're not required for. Incompatible might be if it's only required for the Project state - but in that state it's mandatory.
2. Staus table. This could also be treated as a special project. Illness could be a project, as well as holiday and other reasons to be unavailable. This may not fit in your scenario depending on some of the details, but it's a concept to consider.

Ok then, thanks.

Re: treating the status as a type of project, is it ok to have so many of the table fields left blank (as they would end up in this situation, unless I misunderstand you) ?

It's a balance.
Blank (empty) fields generally take up very little space. As long as your code isn't likely to fall over expecting data in them, it should be ok.
Ultimately it's what you're happy with that matters most in this sort of case.
If you think of the table storing data of the things that time is spent on, rather than projects exactly, it could work for you.

Great, I'll give this way a go. Should I have a field 'projecttype' with three possible values ('tender' 'job' and 'special') to distinguish between them in that case, or should I rely on checking which fields are filled and which fields are not to determine this?

Definitely have a flag type field indicating the ProjectType. That way any queries built to use it interface directly with the data and the WHERE clause doesn't need to refer to calculations (generally to be avoided if possible as it can invalidate optimisations). In fact I'd go as far as to say this field [ProjectType] should probably be included in one of your indexes at least, if not more.

Many thanks for all your help.

Now I only need to solve the problem of having my specification changed every couple of days :)