Relating 3 tables

[matt beamish]

Hi folks. I need to redesign a database to include more complex relationships between 3 variables non of which are mutually exclusive.

These are my 3 tables/fields. Each field is unique in its table.

T_Project.JobNo
T_Accession.AccessionNo
T_Reports.ReportNo

There is no clear relationship between the variables - ie they can all be 1 to 1 or 1 to many or do not have to exist at all.

So I realise now (after 14 years of working with this data), that I need to have what I would think of as holding tables between the primary tables that hold combinations of the variables.

My question is this: Do I do this in one table that holds all 3 variables in non-repeating combinations (although this would need to allow nulls) or do I do it in 3 separate holding tables?

Any advice appreciated.

thanks

 

[plog]

Listing 3 fields suffixed with 'No' and telling us they have relationship to each other, doesn't give us any insight into helping you. Honestly from that description, to put them all in one table, you just put them in one table.

Maybe if you can further explain your issue and demonstrate it with sample data?

 

[jdraw]

I agree with plog --- I suggest you step back ( you may just be too close to the data 14 yrs is a long time) and give us the 30,000 ft overview of the situation, the problem/issue/ new requirement in plain simple English.
Tell us about the business and then how Project, Accession and Reports fit into the mix.

A jpg of your relationships window may be helpful to readers.

 

[matt beamish]

Thanks for replies, and apols if I am not clear enough.

I have Jobnos which identify a piece of work by my organisation with its location and budget. All staff time is recorded below this Jobno.
I have Reportnos which identify and index reports written by my organisation. The report (sometimes) belongs to an archive of material which is deposited with a museum. Perpaps 30% of reports do not belong to an Archive.
I have Accessionnos which are Museum Accession codes. These codes identify an archive of material that we produce, some of these archives contain Reports.

JobNos, ReportNos and AccessionNos are stored in separate tables with unique indexes on respectively JobNo, ReportNo and AccessionNo.

When the database was first put together, the table AccessionNos also had a field for Jobno set with Referential Integrity. The table ReportNos has a field for JobNo also set with Rerential Integrity.

Fundamentally a ReportNo cannot exist without a JobNo and an AccessionNo cannot exist without a JobNo. A JobNo can exist without reference to either a ReportNo or an AccessionNo.

Current relationships allow for 1 Accession to have 1 JobNo and for 1 Report to have 1 JobNo. 1 JobNo may therefore relate to multiple ReportNos. Reports are currently related to AccessionNo through a shared JobNo and this limits the capacity of the database to reflect reality.

The reality that I need to include is this: 1 AccessionNo may relate to multiple Jobnos, and 1 ReportNo may also have multiple Jobnos. 1 AccessionNo may also have multiple ReportNos (this can currently happen). 1 Jobno may in some cases relate to multiple AccessioNos.

So I am considering how to achieve this, so that the database can be used to store these values and allow people to create the records they need to work with.

I know that JobNos are different to both AccessionNos and Reportnos as they can exist without reference to either to the other two, whereas both of the other two need to relate to a Jobno.

I was thinking of having a table sitting between JobNos, AccessionNos ad ReportNos that has 3 fields - JobNo, AccessionNo and ReportNo, with a unique index that includes all three values but am not sure if this is advisable.

Is that any clearer?