Life from the tank

I am mostly wading through Access 2000 for Windows for Dummies. Databases are lamentably a weak spot for me. I have some information which would be much better controlled by using some of the functionality of a relational database.

If anyone can help me clear my mind about these things it would really help….

Things I know :-

The difference between a flat file and a relational database
What fields, records, tables, forms and queries are.
That there can be relationships between tables and these take the form of one to many, many to one and many to many. I even understand what these mean.

But I don't know how to make it work.

I have something I want to do. I have a list of organisations and a list of the requirements they have put forward for our project. So, I thought to myself – I'll have three basic tables

Organisation

Into which I will put

Org ID
Org Name
Org URL

And Contact

Contact ID
Contact Surname
Contact FirstName
Contact Email

And Requirements

Req ID
Req
Req Date
Req Timescale
Req Priority


Now the first thing I want to do is link the Contact to the Organisation. So do I add an Org ID field to the Contact Table (if so do I do it by looking up the Org ID and name in the data type field? If anyone could explain to me exactly what's happening when you do a lookup I'd be grateful!)

Or should I just bung all of the dets into the Organisation Table?

Then I thought a lot about our requirements. The problem may be that I need a many-to-many relationship between the organisation and requirements table… Why? Here goes…

We are recording requirements. They come from other organisations, all of which have a key contact. An org puts forward at least one requirement (but it is often more than that). I know I could record in the requirements table who put this forward by linking to the organisation table.

However it is the case that several organisations have independently put forth the same requirement.

It would be useful to know

What requirements have been put forth and by who

When these requirements were made
(i.e. Tesco put it forth on the 1st March and/or Sainsburys on the 14th March)

Whether these requirements have been put forward by more than one organisation

What priority these requirements have for those orgs that have put them forth

In what timescale the orgs wish to see the requirements fulfilled


If I need a many to many relationship I know you create a linking table between the organisations and the requirements and that you then assign Ord ID and Req ID as a multiple key. I'm not sure what else should be in this linking table and more to the point what I then do with it (populate it… etc). I know I will have to create a query and/or a form.

The worst case scenario is that I simply record which requirements an organisation has of our project but that seems a bit flat. And more to the point duplicates data.

The best case scenario is that I am not thinking clearly and have hideously over complicated things.



Told you it didn't come instinctively to me!