Sub / Supertype advice

Frunkie · #1 (**permalink**) 06-11-07, 21:09

I have a model that consists of about 40 tables and multiple join tables. I have tried and tried and tried to put these tables back together with joins but have been unsuccessful. I am rethinking this model and I can see where in certain situations it would clearly be a benefit to use sub/supertype. At least that's what I *think* would be a benefit. Here is what I have.

1. I have a person entity that holds all of the names in the database.
2. I have an address entity that holds all of the addresses.
3. I have a role entity that holds all of the roles.

I have other tables throughout the db that will use the tables I have mentioned above. For example:

1. A vendor has an address
2. An employee has an address

When I place the PK from the vendor table into the address table, I now have a vendor FK in the address table. Not really too bad if the address table only handled addresses for the vendor. But..

When I place the PK from the employee table into the address table, I now have the FK from the employee table. So..

If I only want to enter an address for an employee I am forced to enter data for the vendor also. WHat I have done to combat that is to use a join table.

Is this a correct way to use the join tables?

I have also noticed where *maybe* IMHO I could use a sub/supertype for the following tables:

Code:

person (Super)

    address(Sub of person)
    role(Sub of person)

        teacher(Sub of role)
        janitor(Sub of role) 
        vendor(Sub of role)

Would this be a good opportunity to use sub/supertype?

I am starting to think that I could almost model the entire db using sub/supertypes.

Is this foolhardy?

r937 · #2 (**permalink**) 06-12-07, 02:37

not really foolhardy, but perhaps misdirected

subtype/supertype structures are useful only when the things being modelled are different types of the same thing

what "thing" is it that people and vendors are instances of?

i can understand the attraction of writing only one set of application routines to handle inserting, updating, and deleting addresses, but then to relate the address back to its "owning" entity, you have two choices:

- have both a person FK and a vendor FK in the address, such that both are nullable, because only one of them will actually have a non-null value in any given row -- this avoids the ugly scenario of have a single "FK" column (which clearly cannot be defined as a real FK) which will hold either the person or the vendor key value

- use a single FK to relate the address to the supertype "thing" which person and vendor are instances of

one simple way to avoid the issue completely is to have a person address table and a vendor address table

as i said elsewhere, the only reason you'd want a single address table is if you had an interest in the address independently of whether it was a person's address or a vendor's address -- in other words, if, like the post office, you needed to keep track of an address even if there is no person or vendor actually currently there

most business apps fail this criterion, and so address is a dependent entity, not an independent entity

the "convenience" of having only one add-an-address and only one change-an-address and only one delete-an-address application module should not dictate table design, and if there are two address tables, the programmer is just going to have to code accordingly, eh

but if you do decide to have a single address table, this pretty much requires that you have a supertype "thing" table which owns the address, such that the subtypes of this "thing" are person and vendor, and that's just too ugly for me to contemplate

pootle flump · #3 (**permalink**) 06-12-07, 03:16

Hmmm. My first thought was that the FKs are in the wrong table. Should be address fk in the various people tables. That is the whole point of having an address entity in the first place.

Frunkie · #4 (**permalink**) 06-12-07, 03:36

Quote:

Originally Posted by r937

what "thing" is it that people and vendors are instances of?

What I have is a model geared around the law enforcement community. The "things" that I see being of super and sub type are:

Code:

Person (Super)
Vendor (sub)
suspect(sub)
victim(sub)
witness(sub)
reporting party(sub)

All of the "roles" these people play have exactly the same attributes. Some of these attributes are hair color, eye color, etc. When we get to suspect, there is a little more information or attributes that are needed such as disposition, SSN, scars, tattoos, etc.

Quote:

i can understand the attraction of writing only one set of application routines to handle inserting, updating, and deleting addresses, but then to relate the address back to its "owning" entity, you have two choices:

- have both a person FK and a vendor FK in the address, such that both are nullable, because only one of them will actually have a non-null value in any given row -- this avoids the ugly scenario of have a single "FK" column (which clearly cannot be defined as a real FK) which will hold either the person or the vendor key value

This sounds really bad to me already. I had tried making one of the FKs nullable and it just didn't seem like the correct or cleanest way it could be done. It was this that pushed me to seek another method.

Quote:

one simple way to avoid the issue completely is to have a person address table and a vendor address table

This is the way I originally had it modeled and the UI guy complained and said that the model was incorrect. Now, I don't think that the addresses are such a big issue; what I mean is that as long as I can pull and distinguish a suspect's home / work / other address from the table, that is all I care about.

Quote:

as i said elsewhere, the only reason you'd want a single address table is if you had an interest in the address independently of whether it was a person's address or a vendor's address -- in other words, if, like the post office, you needed to keep track of an address even if there is no person or vendor actually currently there

most business apps fail this criterion, and so address is a dependent entity, not an independent entity

Well, there is a definate interest in the address. It just has to be distinguishable. I have to distinguish a home from a work address or even a third "other" address. I'm not sure if what my needs are fall into what you are describing or not. But I don't see why, the way I originally had it modeled was "not correct". I could still pull the data down that I needed and still was able to distinguish a vendor's address from a suspect's address a lot easier than I can now.

Quote:

the "convenience" of having only one add-an-address and only one change-an-address and only one delete-an-address application module should not dictate table design, and if there are two address tables, the programmer is just going to have to code accordingly, eh

Absolutley. I think that may be where he is going with this. Actually, I think he was just trying to be helpful.

Quote:

but if you do decide to have a single address table, this pretty much requires that you have a supertype "thing" table which owns the address, such that the subtypes of this "thing" are person and vendor, and that's just too ugly for me to contemplate

My bottom line Rudy is that if you or any other professional on these forums think it is a bad design, then it is. I will just put it back to the way it was.

Unless of course you can see a better way of modeling this. Now that I have posted the actual people and their "roles".

I would just opt to have a vendor address table, a suspect address table et blumin cetra.

r937 · #5 (**permalink**) 06-12-07, 03:48

make sure you distinguish between "role" and "type" in your thinking

for example, male and female are two types of person, guardian is a role that either can play, birth mother is a role that only a female can play

you said there is a definite interest in the address, but i will venture that you will not have an address in your app unless it belongs to someone, so i'm not sure you got my point about dependency

Frunkie · #6 (**permalink**) 06-12-07, 04:01

Quote:

Originally Posted by r937

make sure you distinguish between "role" and "type" in your thinking

for example, male and female are two types of person, guardian is a role that either can play, birth mother is a role that only a female can play

you said there is a definite interest in the address, but i will venture that you will not have an address in your app unless it belongs to someone, so i'm not sure you got my point about dependency

Yes, that point was well taken. When you referred to the address as being "dependant" or "independant" is when it clicked for me.

Thanks for the pointers on the type and role. The example you gave also put it into a great perspective for me. Thanks Rudy!

Quote:

Originally Posted by pootle flump

Should be address fk in the various people tables. That is the whole point of having an address entity in the first place.

Well, not really because each person can have more than one address. That was why I was using join tables.
EDIT:
Actually Pootle, I misunderstood what you said. Yes, you are correct. The address FK should go into the the various people tables. The problem arose when I needed to enter an address for a vendor or someone "other than" the person for whom I was entering a record on. Does that make sense? In other words, if I have 5 different types of people I would have 5 different FKs in my address table. No good.. I would have to make all of those FKs nullable as Rudy pointed out which really couldn't be classified as a FK at all. I think I was trying to do what that guy was trying to do in that post a week ago you told me to read. Remember?

gvee · #7 (**permalink**) 06-12-07, 04:20

Quote:

Originally Posted by fjm1967

My bottom line Rudy is that if you or any other professional on these forums think it is a bad design, then it is.

Professionals get it wrong sometimes too, you know!
Granted, Rudy (and other members here) are very good at this sort of thing - but don't always take what they say as gospel.

That's not me saying that Rudy is wrong in this situation either

Frunkie · #8 (**permalink**) 06-12-07, 04:28

Quote:

Originally Posted by georgev

Professionals get it wrong sometimes too, you know!
Granted, Rudy (and other members here) are very good at this sort of thing - but don't always take what they say as gospel.

Yes, but it is much less likely fr them compared to us eh?

Quote:

That's not me saying that Rudy is wrong in this situation either

lol. Yeah George.. I think you know better.

healdem · #9 (**permalink**) 06-12-07, 05:36

out of curiosity do you expect your model to have a person with mulitple roles

I say this purely out of interest, as I had a real issue on an educational app, where a member of staff was also a student, and not only that was a student doing 2 courses at the same time. it would have been compounded had we looked at the data 2 years earlier when one of her children was at the same education establishment so she would have been a parent/guardian as well.

it didn't help that a member of staff could also be subcategorised into other roles (eg admin, support, academic [tutor, lecturer, head of dept, course elader etc....]

Frunkie · #10 (**permalink**) 06-12-07, 13:25

Quote:

Originally Posted by healdem

out of curiosity do you expect your model to have a person with mulitple roles

I say this purely out of interest, as I had a real issue on an educational app, where a member of staff was also a student, and not only that was a student doing 2 courses at the same time.

Yes Healdem, that is exactly what is happening with this model. For example, a reporting party could also have been victimized making him/her a victim as well as a reporting party.

Quote:

it would have been compounded had we looked at the data 2 years earlier when one of her children was at the same education establishment so she would have been a parent/guardian as well.

could you expand on that a little please. What would you have done differently on that Healdem? I mean, what would you have compounded?

Here is the way I originally had it modeled.

create table person(
first_Name VARCHAR (35) not null
, last_Name VARCHAR (40) not null
, role CHAR (20) not null )

I am using the role field to distinguish between the persons role in the database at that very moment in time.

Do you have any suggestions for me Healdem? Since you have already tackled that issue.

healdem · #11 (**permalink**) 06-12-07, 13:40

In this particular incidence the student registration details were held by a central mainframe (god it sounds opld and this was no more than 4..5 years ago)

the faculty operated their own registration, adminstration and exam monmitoring program.

part of the requirements was to marry up the student registration details with pre-existing data on the local system

this meant we had to permit any person known to the system to have multiple roles. effectively we had an intersection table (at least thats what I think its called.. I call XREF tables associating a single person with the mulitple roles they had in the organisation.

so we had a separate table for role and person
and we had a sub table whose PK was a composite of the PersonID & RoleID

we put a constraint on the sub table to say that each person must have at least one role defined in the subtable , but they could have multiple roles.

Frunkie · #12 (**permalink**) 06-12-07, 14:18

Healdem,

Would it have looked like this?

Here is the DDL:

Code:

Create table person (
	person_Id Char(20) NOT NULL,
	first_Name Char(20) NOT NULL,
	last_Name Char(20) NOT NULL,
	UNIQUE (person_Id),
 Primary Key (person_Id)) ENGINE = MyISAM;

Create table role (
	role_Id Char(20) NOT NULL,
	role Char(20) NOT NULL,
	UNIQUE (role_Id),
 Primary Key (role_Id)) ENGINE = MyISAM;

Create table XREF (
	role_Id Char(20) NOT NULL,
	person_Id Char(20) NOT NULL,
 Primary Key (role_Id,person_Id)) ENGINE = MyISAM;


Alter table XREF add Foreign Key (person_Id) references person (person_Id) on delete  restrict on update  restrict;
Alter table XREF add Foreign Key (role_Id) references role (role_Id) on delete  restrict on update  restrict;

What kind of restraint did you use on the XREF table?

Internet Services

Web Development

Digital Marketing

Consumer Tech