Setting up tables and relationships for database

[sbraz64]

Hi,

I am new to Access...and I'm working on setting up a database for my group. I am struggling to figure out the best way to set up tables and relationships. I'd like to ask for your thoughts on the best way to do this. So my group has 7 divisions. Each division may have multiple types of requirements (contracts, travel, training, equipment, supplies, utilities, or other). Each division may also be responsible for construction projects and to make it more confusing for me, some of the construction projects may have different portions (delineated by tracking #s) that must be kept separate but also called up together as a single project. I need to be able to keep track of ALL of the costs associated with requirements and projects for each division, but I also need to be able to track requirements separate from projects. Both requirements and projects have some fields that are similar but also a LOT of fields that are not similar at all. So, if you were setting this up how many tables would you set up? One for divisions, one for requirements, one for Projects (but how would i be able to pull up the projects with multiple tracking #s as 1 project)?

[PhilOfWalton]

My mug's guide to normalisation is that each table should have information on a single subject that exists independently. A typical example is a names & addresses database.
There should be a table of people with things like names, sex, birthday, hair colour etc. A person is an independent object.
There should be a table of addresses including house number and post (zip) code. One can argue whether the town should be included in the address, my answer is no because again the town exists in it's own right and may or not contain that address.

Now to set up a relationship, you firstly define which town the address is in, then defining who lives at that address. (Could be a number of people.

So to get to your problem. It's a bit short of information so quite a lot of guesswork.

You need a table of Divisions with at least a DivisionID and a DivisionName.

You need a table of Requirements (Don't think that is a very meaningful name) but basically RequirementID and RequirementName.

You need a table of Projects, again ProjectID & Project Name. Now to link the project to the requirement, you also need a RequirementID as a foreign key.

You need a table of Portions with the usual PortionID, PortionName and a foreign key of ProjectID to link it with the project.

Somewhere in the last 2 table, you need to deal with costs.

You may be wondering why I haven't dealt with joining the Division table and the Reqirements table. This is a bit more comlicated.

To do this, you need a table TblJoinDivisionRequirements with a combined key of DivisionID and RequirementID. This allowd each Division to have as many Requirements as necessary and equally any requirement (e.g. training costs) can be linked to the relevant Division.

Hope this starts you off

Phil