August 28, 2007 at 8:53 am
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.
August 29, 2007 at 7:12 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 30, 2007 at 4:04 am
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
August 30, 2007 at 9:17 am
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
August 31, 2007 at 4:11 am
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
August 31, 2007 at 6:07 am
Good point about the name.
Thanks for your comments Denis.
Regards,
Barry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply