http://users.pandora.be/gisaalter/tweakers/knipsel2.jpg

Hi, first of all, I'm new to this forum. I came accross this site in order for some feedback on a design I have made.
The purpose of this design is to keep track of real-esate developments.
I hope the descriptions of the tables are straightforward.

An officer (Officer) is assigned to an area (Area) . In this area, several sites (Site) can be developped. Several types (Type) of buildings can be developed, by several contracters (Builder). Architects (Architect) are assigned to the builders.

That's about it. Thanks for all your feedback, hopefully with your help I can improve the design...

Probably you need to decide whether a builder is assigned to a site or a building. Ditto for architects being assigned to builders or buildings. You need to apply all your relationships.

Can you really phone an area? Who answers it?

Architects are chosen by the builders, independent of the 'area' in which they are. The builders are assigned to the buildings, cause you can have several builders working on the same site, on different types of buildings...
I will have a look how I can optimize it as such, can you give me a clue ?

Good point on the phone, will handle that..
Thx

Ok - so if that is true - why have a builder id in the site table? It is not only redundant it does not represent the relationship. Ditto again for architects although I'm not so clear on your response there.

pootle flump, indeed the builder_ID is empty in the Site table, it can be removed.
The architects have a relationship with the builders (cause they are assigned by the builders) AND to the buildings (cause they design it). So if I understand you correct, I should add an extra relationship architects <-> builders AND to keep the exisiting architects <-> buidings relationship ?Idem ditto for the builders <-> site + builders <-> buildings ?

If you know the builder of a building does it follow then that you will always know the architect too (irrespective of the architect column in the building table)?

Yes, each of the builders have 2 architects assigned, which they can choose from.

So - a builder has up to 2 architects and each building a builder works on will have one of those two architects assigned to it. Correct?

Yes, that's correct

Kewel.

In that case your design just doesn't cut it. So - based on that info - what changes do you think need making? (I'm counting around 4 - quick tot up - not definitive).

I will try do to the following:

Make an extra table Builder_Architect with ArchitectID & BuilderID. That way I assign the architects to the Builders.
Next, I'll add the AreaID to the Buildings, cause they have a relationship to.

Super. Change and post and let's see where we are up to.

Ok, I'm onto it. I'll run some queries on the new design and see if I can find any other deficiencies. Thanks, you are of great help !

Ok, I've made a new design. It's available over here:

http://users.pandora.be/gisaalter/tweakers/knipsel3.jpg

I hope I have understood your remarks.

Very well done. You've obviously looked up many-to-many relationships

Only two questions:
How do you decide which of the builders' architects are assigned to a buildings record?

This is an interesting one:
Can you think of a way in Access to model this cardinality requirement?

EDIT - you are using Access right - I think I just assumed....