One-to-many with a twist

[martind-crap1]

Hi,

What is the best way to model this: Assume I have two objects: Agency
and Publisher, and both have a 1-to-n relationship to Employee. This is
a true 1-to-n relationship, as each Employee can only work for one
Agency or one Publisher. Let's assume further that I cannot introduce a
supertype (e.g. Employer) which holds the 1-to-n relationship.

My preferrd solution is to have a foreign key in Emplyee that can
either link to a primary key of Agency or Publisher (all my primary
keys are 64-bit IDs that are unqiue across the database). However, now
I won't be able to map a bi-directional association, without indicating
in Employee whether this is an "Agency" or "Publisher" relationship
(ala <ANY>).

My other option is to use two tables, AgencyEmployee and
PublisherEmployee, which can then be linked as traditional 1-to-n
bidirectional associations.

What do you guys consider best practice in this situation?

Cheers,

Jen

[David Portas]

It would probably be a mistake not to create an "Employers" table.
Especially since you almost certainly have some attributes common to Agency
and Publisher. Otherwise splitting that entity into named tables looks like
a weak design. I would probably go for something like this:

CREATE TABLE Employers (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
NOT NULL CHECK (emp_type IN ('A','P')), UNIQUE (employer_id, emp_type),
emp_name VARCHAR(35) NOT NULL UNIQUE)

CREATE TABLE Agencies (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
DEFAULT 'A' NOT NULL CHECK (emp_type = 'A') /* , ... other columns specific
to Agencies */, FOREIGN KEY (employer_id, emp_type) REFERENCES Employers
(employer_id, emp_type))

CREATE TABLE Publishers (employer_id INTEGER PRIMARY KEY, emp_type CHAR(1)
DEFAULT 'P' NOT NULL CHECK (emp_type = 'P') /* , ... other columns specific
to Publishers */, FOREIGN KEY (employer_id, emp_type) REFERENCES Employers
(employer_id, emp_type))

CREATE TABLE Employees (... employer_id INTEGER REFERENCES Employers
(employer_id), ...)

--
David Portas
SQL Server MVP
--

[martind-crap1]

Thanks!

These are not my "real" objects, my actual object hierarchy is much
more complex. I can't introduce a superclass because that would
require multiple inheritance. What's my 2nd best option?

[David Portas]

> that would

require multiple inheritance.

Try not to conceptualize with terms like "inheritance", which are pretty
meaningless in an relational data model. Relational modelling isn't OO
programming. If you explain how it is that your design doesn't meet your
requirements I'm sure we can suggest alternatives. Arguably there is no "2nd
best option" if it's done right.

--
David Portas
SQL Server MVP
--

[David Portas]

Oops:

explain how it is that MY design doesn't meet your requirements

--
David Portas
SQL Server MVP
--

[Hugo Kornelis]

On 30 May 2005 09:39:26 -0700, ma***********@mailblocks.com wrote:

Hi,

What is the best way to model this: Assume I have two objects: Agency
and Publisher, and both have a 1-to-n relationship to Employee. This is
a true 1-to-n relationship, as each Employee can only work for one
Agency or one Publisher. Let's assume further that I cannot introduce a
supertype (e.g. Employer) which holds the 1-to-n relationship. (snip)What do you guys consider best practice in this situation?

Hi Jen,

I'm with David - the best solution is to defy the assumption and to
introduce the supertype table Employer.

But if I suspend my disbelief and assume that there actually is a valid
reason for not doing that, than I'd go with

CREATE TABLE Employees
(SSN char(9) NOT NULL,
AgencyCode char(4) DFEFAULT NULL,
PubNum int DEFAULT NULL,
..... (other columns),
PRIMARY KEY (SSN)
FOREIGN KEY (AgencyCode) REFERENCES Agencies (AgencyCode)
ON DELETE NO ACTION ON UPDATE CASCADE,
FOREIGN KEY (PubNum) REFERENCES Publishers (PubNum)
ON DELETE NO ACTION ON UPDATE CASCADE,
CHECK ((AgencyCode IS NULL AND PubNum IS NOT NULL)
OR (AgencyCode IS NOT NULL AND PubNum IS NULL))
)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[martind-crap1]

Thanks for the input, guys! I'm looking to go for maximum flexibility
without having to abandon 1-to-n in favor of n-to-m, so here's another
option I'm contemplating: What if I include a discriminator in
Employee that specifies whether the parent_id references an Agency or a
Publisher? Foreign key constraints are no longer possible, but hey,
can't have it all...
One specific case are Address objects, which I want to back-link to
their respective owners (Agents, Publishers, Customers, Suppliers, etc.
all have addresses, and I can't find a good superclass that won't screw
up my ORM).

What do you think about that?

Thanks!

[Damien]

ma***********@mailblocks.com wrote:

Thanks!

These are not my "real" objects, my actual object hierarchy is much
more complex. I can't introduce a superclass because that would
require multiple inheritance. What's my 2nd best option?

One that I've done in the past (not sure on others opinion on this, it
may be "sinful") is as follows:

Create Database DBTest
go
use DBTest
go
Create Table Parent1 (
Parent1ID int not null PRIMARY KEY
)
go
Create Table Parent2 (
Parent2ID int not null PRIMARY KEY
)
go
Create Table ChildOfParent1Or2 (
ParentID int not null PRIMARY KEY,
Parent1ID int null,
Parent2ID int null
)
go
alter table ChildOfParent1Or2 add constraint
FK_ChildOfParent1Or2_Parent1 FOREIGN KEY
(
Parent1ID
)
references Parent1
(
Parent1ID
)
go
alter table ChildOfParent1Or2 add constraint
FK_ChildOfParent1Or2_Parent2 FOREIGN KEY
(
Parent2ID
)
references Parent2
(
Parent2ID
)
go
alter table ChildOfParent1Or2 add constraint
CK_ChildOfParent1Or2_ValidParents CHECK
(
(Parent1ID is null and ParentID = Parent2ID and not Parent2ID is
null) or
(Parent2ID is null and ParentID = Parent1ID and not Parent1ID is
null)
)
go

So okay, it is sinful, but it allows you to maintain proper foreign key
relationships whilst ensuring that there is one and only one such
relationship applied. Obviously, for more parents, the final Check
constraint gets larger and larger, and more and more unweildy. You
could split the (ensure one and only one column is not null) check out,
and then check that your ParentID is valid using COALESCE.

[Hugo Kornelis]

On 31 May 2005 01:40:30 -0700, ma***********@mailblocks.com wrote:

Thanks for the input, guys! I'm looking to go for maximum flexibility
without having to abandon 1-to-n in favor of n-to-m, so here's another
option I'm contemplating: What if I include a discriminator in
Employee that specifies whether the parent_id references an Agency or a
Publisher? Foreign key constraints are no longer possible, but hey,
can't have it all...
Hi Jen,

I don't like this idea. Don't mess up your referential integrity. Some
day, it will bite you - and it'll bite you HARD.

I still recommend using a supertype. And if the entities are really
totally unrelated, then I'd use seperate columns (as shown in my
previous post) for the relationships.

I don't understand the "abandon 1-to-n in favor of n-to-m" part of your
message - how does my proposed design abandon 1-to-n?

Final note: if you have to expand my design to have more than two
mutually exclusive 1-to-n relationships, then you might wish to rewrite
the CHECK constraint to a more expandable form:

CHECK ((CASE WHEN AgencyCode IS NULL THEN 1 ELSE 0 END
+ CASE WHEN PubNum IS NULL THEN 1 ELSE 0 END
-- room for expansion is here
) = 1)

One specific case are Address objects, which I want to back-link to
their respective owners (Agents, Publishers, Customers, Suppliers, etc.
all have addresses, and I can't find a good superclass that won't screw
up my ORM).

Aren't Agents, Publishers, Contacts and Suppliers all Relations? Or
(depending on your business) Roles of Relations?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

(ma***********@mailblocks.com) writes:

Thanks for the input, guys! I'm looking to go for maximum flexibility
without having to abandon 1-to-n in favor of n-to-m, so here's another
option I'm contemplating: What if I include a discriminator in
Employee that specifies whether the parent_id references an Agency or a
Publisher? Foreign key constraints are no longer possible, but hey,
can't have it all...
Discriminants? Toto, you are not in Kansas anymore. In a relational
database, discriminants are virtually unheard of, but referential
integrity is essential.
One specific case are Address objects, which I want to back-link to
their respective owners (Agents, Publishers, Customers, Suppliers, etc.
all have addresses, and I can't find a good superclass that won't screw
up my ORM).

That's probably because it would be the wrong way to go. An agent,
publisher, customer etc could have an address, and each such table
would include an addressid. But the address would be blind to from
where it is referred.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[martind-crap1]

These are all very good thoughts, thanks. What happens if I have
multiple n-to-1 relationships to the same child (e.g.
Publisher-Address, Agency-Address -- but Addresses are never shared
among entities) and I want to make them bidirectional?

Items to consider:
- All primary keys are enterprise keys (unique across all associated
databases and tables).
- Only one application has write-access to my data model
- I want a data model that is resilient to change ... if I introduce
another entity with an Address, I don't want to have to add another
foreign key column

What are some potential downsides to giving up referential integrity?

Thanks!

[David Portas]

> multiple n-to-1 relationships to the same child (e.g.

Publisher-Address, Agency-Address -- but Addresses are never shared
among entities) and I want to make them bidirectional?
I share Erland's puzzlement at your terminology: "Inheritance",
"Discriminants" and now "Bidirectional". These are not relational database
concepts and have nothing to do with your problem. I think you are making it
seem much more complex than it needs to be because you aren't evaluating it
in purely relational terms. It's very difficult to help you further because
you haven't given us any relevant explanation of what's wrong with our
previous suggestions.

What are some potential downsides to giving up referential integrity?

That's a question you should be able to answer yourself. How much is data
integrity worth to you or your organization?

--
David Portas
SQL Server MVP
--

[Erland Sommarskog]

(ma***********@mailblocks.com) writes:

These are all very good thoughts, thanks. What happens if I have
multiple n-to-1 relationships to the same child (e.g.
Publisher-Address, Agency-Address -- but Addresses are never shared
among entities) and I want to make them bidirectional?

Addresses are admittedly somewhat difficult to handle. On the one hand,
the address of a publisher and the address of an agency could be considered
to be different objects. On the other, you may want to have a common
handling of addresses, and not duplicate it for the same entity.

What we do is that have have a table "addresses", and it can be referred
to by several entities. In our case, it is perfectly possible that an
address is shared. For instance, an auditor may recieve copies of a client's
account statement, but the auditor may himself also be a client.

The main problem with our arrangement as I see is not that an address
could incorrectly be shared between two entities when it shouldn't, but
that the same address is entered twice, because it's difficult to find a
good method for users to find an existing address.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

Maybe the addresses you are using are never shared, but (unless this is
a business rule), this is purely coincidental. An address is an entity.
And with a foreign key relation you can specify that a publisher or an
agency has an address. It is not even uncommon to have several such
relations between two entities. For example, a publisher could have a
billing address and a shipping address. That would mean two
relationships to (potentially) the same address.

Looking at the address as being "properties" of a publisher is not the
way a relational database works.

If it is a business rule that addresses "must not be shared" then that
is a different matter which is probably best solved by writing a
trigger.

Gert-Jan
ma***********@mailblocks.com wrote:

These are all very good thoughts, thanks. What happens if I have
multiple n-to-1 relationships to the same child (e.g.
Publisher-Address, Agency-Address -- but Addresses are never shared
among entities) and I want to make them bidirectional?

Items to consider:
- All primary keys are enterprise keys (unique across all associated
databases and tables).
- Only one application has write-access to my data model
- I want a data model that is resilient to change ... if I introduce
another entity with an Address, I don't want to have to add another
foreign key column

What are some potential downsides to giving up referential integrity?

Thanks!

[Erland Sommarskog]

Gert-Jan Strik (so***@toomuchspamalready.nl) writes:

Maybe the addresses you are using are never shared, but (unless this is
a business rule), this is purely coincidental. An address is an entity.
And with a foreign key relation you can specify that a publisher or an
agency has an address. It is not even uncommon to have several such
relations between two entities. For example, a publisher could have a
billing address and a shipping address. That would mean two
relationships to (potentially) the same address.

Good point. For instance, in our database we have a table
"customeraddresses", which thus permits a customer to have several
addresses. This connection table then contains flags to mark addresses
with certain properties.

Then again, for other entities we only have room for one single address.
Exactly how this looks like, depends on the business rules.
It's also interesting to note that an address simply have nothing which
is anywhere close to a natural key.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Damien]

Erland Sommarskog wrote:
[snip]

It's also interesting to note that an address simply have nothing which
is anywhere close to a natural key.

Latitude, Longitude, Altitude and datum? :-)

[--CELKO--]

>> It's also interesting to note that an address simply have nothing which is anywhere close to a natural key. <<

The book trade has the SAN (Standard Address Number) and I believe that
delivery companies also use somethng like this.

[Erland Sommarskog]

--CELKO-- (jc*******@earthlink.net) writes:

The book trade has the SAN (Standard Address Number) and I believe that
delivery companies also use somethng like this.

Maybe they have. But since an address may be a partial address, for
some means of contact, this may not do. We permit users to add an
address which is only an email or a telephone number.

Delivery companies tend to avoid post-office boxes.

An address is not only street name/post office box, zip code and city,
but could includes the name. For instance, say that a copy of my account
statement goes to my account statement goes to my auditor, and the
address goes:

Erland Sommarskog
c/o Janssons Revisionsbyrå
Stora lillgatan 23
234 34 MELLANSTAD

An address could also include an attention line, or a department
specification etc.

And even if the address is just street address and number, I have no idea
my home has any "SAN" - even less what it would be.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[martind-crap1]

The reason I'm using OO terminology instead of modeling the DB in
purely relational terms is because i want to introduce a standardized
object-relational mapping approach. I believe that although this
introduces a bit more complexity upfront, it will save development time
and costs in the long run...

[Erland Sommarskog]

(ma***********@mailblocks.com) writes:

The reason I'm using OO terminology instead of modeling the DB in
purely relational terms is because i want to introduce a standardized
object-relational mapping approach. I believe that although this
introduces a bit more complexity upfront, it will save development time
and costs in the long run...

I don't exactly what your plans are, but relational databases are
not that very object-oriented. Sure, some knowledge and experience
of object-oriented design can be an advantage, as far as it helps you
to recognize some patterns. But in the end, you should work in the
relational domain you are in. You might be able to save some development
time doing it your way - until you find that you have 20 GB and your
design does not scale.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp