How best to normalise a funding table

Question

How best to normalise a funding table

barry555

SSC Eights!

Points: 944
More actions
August 28, 2007 at 8:53 am

#176458

In my scenario, there are a number of distinct entity types, of which some are:

Pupils
Carers (parent, guardian, social worker)
Tutors
Courses
PupilCourses
Schools
Local Authorities (a.k.a local councils)
Sponsors

Each course a Pupil attends is funded by one or more of Carers, Schools, Local Authorities and Sponsors. For example, a Pupil may attend a Course that costs £100. This may be funded by £20 from Carer A, £15 from Carer B, £40 from School C and £25 from Sponsor D.

Carers, Schools, Local Authorities and Sponsors exist in their own right, not just as funders. For example, a pupil’s carers have to be recorded so they can be written to for permission for something, but they may not fund a single course (some pupils get a full bursary). The Local Authority that a pupil attends has to be recorded (mainly for statistical purposes), but again the Local Authority may not fund the pupil. And so on.

My question is how to store this funding information in a Funding table.

Option 1 (though not serious!):

FundingID (PK), PupilCourseID, CarerID, SchoolID, LocalAuthorityID, SponsorID, StartDate, EndDate, Amount.

I can improve on this by using the idea of supertypes/subtypes. With two supertypes, Person (and subtypes of Pupil, Carer and Tutor) and Organisation (and subtypes of School, Local Authority and Sponsor). So Option 2 could become:

FundingID (PK), PupilCourseID, PersonID, OrganisationID, StartDate, EndDate, Amount.

However, this still means there will always be a redundant column for each row. So what are the alternatives:

Option 3:

Have two funding tables (just got to remember to always union the two when querying funding!),

PupilCourseID (PK), PersonID (PK), StartDate (PK), EndDate, Amount
PupilCourseID (PK), OrganisationID (PK), StartDate (PK), EndDate, Amount

Option 4:

Have three tables, one funding and two linker,

FundingID (PK), PupilCourseID, StartDate, EndDate, Amount

FundingID (PK), PersonID (PK)
FundingID (PK), OrganisationID (PK)

Any comments and/or other ideas gratefully received.

Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736198 More actions · Answer 1

I like either 3 or 4 the best, though I might actually create a view for the purposes of querying that unions the two different tables together.

Wapper SSC Enthusiast Points: 136 More actions · Answer 2

What I would consider is adding a level of abstraction for persons and organisations by introducing a common table "Party" that would incorporates all entities that can be used in similar way. For example, as a funder, or as an addressee, or a contract partner.

This would look like

CREATE TABLE party (party_id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(255))

CREATE TABLE person (party_id INT PRIMARY KEY, name VARCHAR(255), birth_date DATETIME)

CREATE TABLE company (party_id INT PRIMARY KEY, name VARCHAR(255), tax_number VARCHAR(50))

When you create a new entry, you first insert a row into party table, then insert a row with same party_id into corresponding data table. With this approach, your funding table would look like

FundingID(PK), PupilCourseID, FundingPartyID, Amount

or

PupilCourseID(PK), FundingPartyID(PK), Amount

There might be additional considerations for retrieving information relevant to a particular type of "party" in SELECT queries. You might want to create a common view for all party entries, to be used in SELECT queries and reports.

BR,

Denis

barry555 SSC Eights! Points: 944 More actions · Answer 3

Hi Denis,

I had thought of the approach you suggested, but something knaws at me that this isn't doing things the 'proper' way.

By proper, I mean that an ERD should model the real world. The method you suggest would make an artificial entity (in fact, we may as well call the table Entity!) just to generate a unique id for all other entities.

Also, you’ve repeated the attribute ‘name’ in both Party and the sub-types Person and Company. Is this a mistake? Also, another problem is that a Person’s name should be broken down into it’s constituent parts, i.e. FirstName, MiddleNames and Surname. So, again, we would be creating an artificial attribute by using Name as an attribute in Party.

Regards,

Barry

Wapper SSC Enthusiast Points: 136 More actions · Answer 4

Barry,

I personally don't see any problems about this approach. The real world is that a contract can be made with a company or a person. These entities have different attributes but this is not important in terms of the contract. If you are composing a list of items that you personally possess, this could include a whole lot of different things like a house, a car, a dog, a wife (erm, bad example). But in terms of given relation, those are all objects that you have. You can think of it as a role, each item has a role "Object that can be possessed", and the relation is between you and the role.

Back to the problem, I remember that at least some ERDs systems allow you to use "member" entites (don't remember the exact name) that are a part of parent entity and only host those attributes that are unique to them. The common attributes are in the host entity.

The party.name column is a common property (each party should have a name) and is added mostly for convenience, since the name is needed for many queries and lists. If you need more details about the name, you go to the Person table and look it up.

BR,

Denis

barry555 SSC Eights! Points: 944 More actions · Answer 5

Good point about the name.

Thanks for your comments Denis.

Regards,

Barry