Extending a many to many relationship

[peterw]

Hi

I’m returning to Access after a long break, and I’m remembering that the trickiest thing of all can be getting your tables and relationships right. I’d be grateful for any insights into how I can tackle the following brief.

I have to create a database that links people to applications for a project funding scheme. Each person can make one or more applications. Each application can have one or more persons named on it, since applications can come in from single individuals or teams of named individuals. A person named on an application can be either a Sole Applicant, a lead Applicant, or a Collaborator on any given application.

So far so good - I can model the situation with a Many-to-Many relationship as follows

tblPerson
-------------
PersonID (PK)
FirstName
LastName
EmailAddress


tblApplication
-------------------
ApplicationID (PK)
Title
ApplicationField1
ApplicationField2
etc …

tblPersonApplication (the junction table)


---------------------------
PersonaApplicationID (PK)
PersonID (FK)
ApplicationID (FK)
Role

From here I can build a query and form to update all three tables at once, and I can report on persons and their application history which is one of the main purposes of the database.

Here is where it gets complicated (for me). There probably won’t be just one funding scheme, there will be several, each with different application forms recording different things. The relationships described above will still have to hold between people and applications, but now there will be an Application for Funding Scheme A, and Application for Funding Scheme B, and so on.

Because the applications will likely be quite different, I assume they will need to have their own tables. To give trivial examples:


tblApplicationFundingSchemeA
-------------------
ApplicationFundingSchemeAID (PK)
Title
Date
Age
Hair colour
Height
…

tblApplicationFundingSchemeB
-------------------
ApplicationFundingSchemeBID (PK)
Title
Date
Eye colour
Shoe size
Weight
…


My instinct tells me it’s not a good idea to put all possible fields into one giant Application table, as this will result in a huge number of null values over time for one thing. But I can’t see how to work this requirement into my schema. If applications for different schemes have to be stored in different tables, I particularly can’t see how I could ever generate a query that would show me, for example

Person	Scheme	Date	Title	Role
Bob	A	02/09/2013	Clinical skills lab development	Lead Applicant
Bob	B	09/16/2014	Advanced Life support seminars	Collaborator

Hopefully I’ve explained the requirements clearly – please tell me if not. How could I go about modifying my schema to meet them?

Many thanks in anticipation

Peter

[June7]

Consider:

tblApplication

tblApplicationFundingScheme
ID
AppID
Title
FundDate

tblApplicationSchemeDetails
SchemeID
DataDesc
DataValue

This would mean all DataValue inputs would have to be text type. You show height and weight. Will there be numeric values?

[peterw]

Hi June7

Thanks for your reply, I hadn't considered this. There would need to be a range of data types though. My little examples were just for illustration, but there will indeed be things on each type of application that I'd want to store as numeric, date, hyperlink, currency etc., I guess just as if I were maintaining a separate database for each Application type. Would that not work with your idea?

Thanks
Peter

Consider:

tblApplication

tblApplicationFundingScheme
ID
AppID
Title
FundDate

tblApplicationSchemeDetails
SchemeID
DataDesc
DataValue

This would mean all DataValue inputs would have to be text type. You show height and weight. Will there be numeric values?

[June7]

What data entities would be common to all funding schemes? Those would be in tblApplicationFundingScheme.

For others will have to decide whether to use a Details table as I show or multiple tables for each data entity or a flat file for the details.

It is a balancing act between normalization and ease of data entry/output. At some point might just have to declare 'enough! - I can tolerate some empty fields in records because that suits my needs'. Normalize as long as it helps but when it hurts, stop.

I have a db that probably breaks every normalization rule in the book - but it works and accomplishes what we need, which is to provide output of lab test data. Our main interest is to just report the results, we do very little data analysis. Even if need for special data analysis comes up, there are methods to deal with non-normalized data, which I do employ for graphing output on reports.

[peterw]

Thanks, it's reassuring to know there isn't necessarily a "best" approach here. I think on balance I will go with a multiple tables approach, storing each application's unique details in its own table, linked to a central Application table containing as many common fields as possible. The fact that I will need to retain the data types for creating queries on various fields makes this sound like the most appropriate solution for me.

Thanks again for helping me think this through.

Regards
Peter

What data entities would be common to all funding schemes? Those would be in tblApplicationFundingScheme.

For others will have to decide whether to use a Details table as I show or multiple tables for each data entity or a flat file for the details.

It is a balancing act between normalization and ease of data entry/output. At some point might just have to declare 'enough! - I can tolerate some empty fields in records because that suits my needs'. Normalize as long as it helps but when it hurts, stop.

I have a db that probably breaks every normalization rule in the book - but it works and accomplishes what we need, which is to provide output of lab test data. Our main interest is to just report the results, we do very little data analysis. Even if need for special data analysis comes up, there are methods to deal with non-normalized data, which I do employ for graphing output on reports.

[June7]

A table for each Application is NOT what I suggested and is an arrangement I would not consider an option. I suggested a separate table for each detail element (or groups of elements) if you want to avoid 'big flat file' table with a lot of empty fields.

[peterw]

Okay, apologies, I misunderstood what you meant by "multiple tables for each data entity".

Why would you not consider this an option though? Based on what I thought you meant, I drew up the following schema which looks like offering a reasonable compromise in that it preserves data typing, and won't generate empty fields in a single big flat file table.

tblPerson
-------------
PersonID (PK)
FirstName
LastName
EmailAddress


tblApplication
-------------------
ApplicationID (PK)
ApplicationType
ApplicationTitle
[any other common fields]

tblPersonApplication (the junction table)
---------------------------
PersonApplicationID (PK)
PersonID (FK)
ApplicationID (FK)
Role

tblApplicationType_1 [Containing the fuller details for Applications of Type 1]
------------------------
ApplicationType_1_ID (PK)
ApplicationID (FK from tblApplication)
Field1
Field2
...


tblApplicationType_2 [Containing the fuller details for Applications of Type 2]
------------------------
ApplicationType_2_ID (PK)
ApplicationID (FK from tblApplication)
Field1
Field2
...



Would different tables for different detail elements, or groups of elements, be significantly different from this? I'm keen to know if I'm still misunderstanding you and if the above approach isn't going to work for any reason.

Thanks again

[June7]

Sorry, wrong choice of jargon.

What if you wanted to know how many applications of each type? Would have to query how many tables?

Also, you have a circular reference because each tblApplicationType_X can be linked to two tables (tblApplication and tblPersonApplication) and those two tables can link to each other. Maybe this is unavoidable but review http://www.codeproject.com/Articles/...atabase-Design

Can a single application be more than one type?

[peterw]

Each application would be of one type only, so a query based on tblApplication should return a count of Applications of each type. The additional tables linked to tblApplications - effectively one to one - would only be holding the various details specific to each type.

Thanks for the link, I'll check it out.

[June7]

Didn't see ApplicationType field.

Okay, then maybe this does follow what I meant by 'groups of elements'.

Another consideration is that queries, like tables, are limited to 255 fields. A query to retrieve every application will have to include all the Type tables in order for all data to be available for every application record. The query will look like the 'big flat file'.

[peterw]

Okay, that's encouraging.

I'll keep that in mind about query limitations, though I envisage returning the full records for different application types using separate queries too.

Thanks again.

[June7]

I ran into table and field limits for queries with a couple of reports. Resolution was use of subreports.

Good luck.