I'm in the process of redesigning a database after encountering some relationship problems which caused too much confusion.

I've been drawing out my tables with help of online tutorials and I've been trying to normalise as much as possible at the moment. I'm not sure if I've linked the foreign keys correctly, as my main table contains 8 FKs.

Is there another way that the relationships and tables should be done?

If you need an explanation on how it all is supposed to link, just ask and I'll be happy to say. Cheers!

Could you please explain what process/application you are trying to model? The table structure you present does not look correct. The organization, branch and contact tables look OK. For the e-mail and phone tables, I would have a contact method table

tblContactMethods
-pkContactMethodID primary key, autonumber
-fkContactID foreign key to tblContacts
-fkContactTypeID foreign key to tblContactTypes
-txtContact (text field to hold the actual phone #, fax #, cell phone#, e-mail address etc.)

tblContactTypes (a table to hold the contact types Phone#, fax # etc.--1 record for each type)
-pkContactTypeID primary key, autonumber
-txtContactType

As to your dataset table, it looks like you are trying to capture a series of dates. If whatever you are tracking has many actions/dates associated with it then it describes a one to many relationship. I will use items to represent whatever you are tracking.

tblItem
-pkItemID primary key autonumber
-txtItem

tblItemTransactions
-pkItemTransID primary key, autonumber
-fkItemID foreign key to tblItem
-dteTrans (transaction date)
-fkActionID foreign key to tblActions

tblActions
-pkActionID primary key, autonumber
-txtAction

tblActions would hold a record for each type of transaction such as requested, received, approved etc.

I'm not sure about the rest of the structure, so you'll have to provide more details as to what you are trying to do.

Apologies for the late reply, I was away all weekend, but thanks for the reply.

The overall function of the database is to record information on incoming sets of data. For each dataset I need to record who sent it, and the dates of the process for requesting, receiving, formatting and so on. So for each data 'status' I need to record a date, purely for querying reasons, e.g. if a dataset was supposed to be sent on a certain date, the user can then query the date that the dataset was requested on. Currently I have each status as a check box that fills the current date in a text box, this is just to make it easier and quicker for the user to enter the information onto a form.

The format that you posted for the contact types, does that mean that on a form I could only save one txtContact for each contact at a time?

The format that you posted for the contact types, does that mean that on a form I could only save one txtContact for each contact at a time? No, you would use a subform in your main form. In the subform you can display as many detail records as you have for each contact.

For each dataset I need to record who sent it, and the dates of the process for requesting, receiving, formatting and so on. So for each data 'status' I need to record a date, purely for querying reasons, e.g. if a dataset was supposed to be sent on a certain date, the user can then query the date that the dataset was requested on.

Techinically, having many actions and associated date fields is not a normalized design. If a dataset has many actions/dates associated with it, it describes a one-to-many. What would happen if you had to record an additional action? You would have to redesign you table and all associated forms, queries and reports. Each action/date associated with a dataset should be a record (not a field) in a separate but related table. With this setup, you would again use a subform to record the actions/dates related to the dataset.

That actually makes more sense, making a table for each action and date, I never thought of doing that. Thanks very much for the advice!

Ok, I have created tblDataStatus, which holds:
DataStatusID - ID
DataStatusText - Status Text/Name
StatusCheckbox - Checkbox to link to a date

A table called tblDataStatusDates:
DataStatusDatesID - ID
DataStatusDate - Text, will be empty field, filled when checkbox is ticked.

So is this a better structure for what I'm trying to do? Will I put a fkDataStatusDateID in tblDataStatus or do they need to be related in a 'link' table?

If a status can have multiple dates associated with it then your structure would be correct. If a status has only 1 date associated with it then the date and the status should be in the same table. I assume that the status must be related to dataset, but I don't see a foreign key to you dataset table. Out of curiosity, why are you using a text field for the date rather than a date field?

If a status can have multiple dates associated with it then your structure would be correct. If a status has only 1 date associated with it then the date and the status should be in the same table. I assume that the status must be related to dataset, but I don't see a foreign key to you dataset table. Out of curiosity, why are you using a text field for the date rather than a date field?

I'll put the date field in the same table as tblDataStatus, as there will only be 1 date for each status.

I've put a foreign key in the dataset table now, the first relationship image I posted didn't have it because I didn't have the table normalised.

No reason for using a text field really, in the old database I had a text field with an input mask for a date on it, though it didn't really matter at the time. I can change it now anyway.

I've put a foreign key in the dataset table now

If the dataset has many statuses/dates then the foreign key goes in the status/date table

tblDataStatus
DataStatusID - ID
fkDataSetID foreign key to your data set table
DataStatusText - Status Text/Name
StatusCheckbox - Checkbox to link to a date
DataStatusDate

I've got that done, thanks very much. I'm very grateful for all your help here :)

You're welcome!