I've got some data I am unsure how to design tables for. It is an
inheritance type relationship.

I have prospects - which is pretty much anyone the company knows about
and can market to.
I have clients - which is someone who actually logged on to the
website and registered.

prospects can come from lists or perhaps someone refered them etc.

The problem is while they both share common data fields such as name,
address, phone number, email address, etc. The rules are different.
For instance, a prospect is not required to have any of that
information, we take what we can get. On the other hand a client must
have all of the information.

So I ended up duplicating tables for that information only to create
the difference in rules. A client - address relationship is 1 to many
and a prospect - address relationship is 0 to many.

It really doesn't make sense to me to do it that way, because come to
find out, prospects later become clients....

How does one handle such a thing when designing tables? Should I just
relax the constraints for clients and let the data sources handle
validity?

cpisz <christopherpisz (AT) gmail (DOT) com> writes:

That's an object-oriented description, which doesn't map easily to the
relational model. You'll do best when confronting such problems to
re-frame the problem in terms of the relational model.

Okay. Both of these describe people; so a likely normalisation to be
made here is to have a ‘person’ relation containing the details common
to any person you want to track.

So you'll want a ‘prospect’ table, listing people who are prospects;
this table will allow the person record to be missing any details.

You'll also want a ‘client’ table, listing people who are clients; this
table will have a constraint enforcing any listed person to have all
details present.

No, integrity and validity of data is the responsibility of the
database, so it's best to implement it there.

What you describe is a constraint on the database. Many such constraints
can be limited to the fields of a single table (e.g. checking that a
begin-date is not later than an end-date, or even the ubiquitous UNIQUE
and NOT NULL constraints). However, these are just special cases of
database constraints; what you describe will need to be a constraint
that is aware of multiple tables.

Here's one possible solution (PostgreSQL), implementing a function and
setting a trigger to enforce the constraint:

=====
CREATE TABLE person (
id SERIAL NOT NULL,
name VARCHAR NULL,
address VARCHAR NULL,
phone VARCHAR NULL,
email VARCHAR NULL,
PRIMARY KEY (id));

CREATE TABLE prospect (
person_id INTEGER NOT NULL REFERENCES person (id),
-- ... potentially other fields specific to prospect relation ...
PRIMARY KEY (person_id));

CREATE TABLE client (
person_id INTEGER NOT NULL REFERENCES person (id),
-- ... potentially other fields specific to client relation ...
PRIMARY KEY (person_id));

CREATE FUNCTION verify_client_has_all_required_fields ()
RETURNS TRIGGER
AS $func$
DECLARE
person_rec RECORD;
BEGIN
-- Get the corresponding person record.
SELECT id, name, address, phone, email
INTO person_rec
FROM person
WHERE id = NEW.person_id;
IF NOT FOUND THEN
RAISE EXCEPTION
'person id % does not exist', NEW.person_id;
END IF;

-- Verify the person record has all the details needed for a
-- client.
IF person_rec.name IS NULL THEN
RAISE EXCEPTION 'person name must not be NULL';
END IF;
IF person_rec.address IS NULL THEN
RAISE EXCEPTION 'person address must not be NULL';
END IF;
IF person_rec.phone IS NULL THEN
RAISE EXCEPTION 'person phone must not be NULL';
END IF;
IF person_rec.email IS NULL THEN
RAISE EXCEPTION 'person email must not be NULL';
END IF;

-- Return the new client record for further processing.
RETURN NEW;
END;
$func$ LANGUAGE plpgsql;

CREATE TRIGGER client_has_all_required_fields
BEFORE INSERT OR UPDATE ON client
FOR EACH ROW
EXECUTE PROCEDURE verify_client_has_all_required_fields();
=====

This way, all the details about a person are recorded in the ‘person’
table. The ‘prospect’ table references each prospect as a ‘person’; you
could have other fields specific to being a prospect.

The ‘client’ table also references each client as a person; but it has a
constraint which verifies any record must reference a ‘person’ record
that has values for the required fields.

The result is a database that allows ‘prospect^'9 records to be missing
any of the nullalbe fields for a person, but ‘client’ records cannot be
inserted if the required fields have no value. I have configured my
client to show NULL columns as “‼NULL‼� so they stand out better:

=====
('Joe Schmoe', '123 Credibility Street, Frogville', '987-765-5432', 'joe (AT) example (DOT) org'),
(NULL, '468 Adequate Place, Margton', NULL, NULL);
id | name | address | phone | email
----+------------+-----------------------------------+--------------+-----------------
1 | Fred Nurk | ‼NULL‼ | ‼NULL‼ | ‼NULL‼
2 | Joe Schmoe | 123 Credibility Street, Frogville | 987-765-5432 | joe (AT) example (DOT) org
3 | ‼NULL‼ | 468 Adequate Place, Margton | ‼NULL‼ | ‼NULL‼
(3 rows)

DETAIL: Key (person_id)=(0) is not present in table "person".
INNER JOIN person ON person_id = person.id;
id | name | address | phone | email
----+------------+-----------------------------------+--------------+-----------------
1 | Fred Nurk | ‼NULL‼ | ‼NULL‼ | ‼NULL‼
2 | Joe Schmoe | 123 Credibility Street, Frogville | 987-765-5432 | joe (AT) example (DOT) org
3 | ‼NULL‼ | 468 Adequate Place, Margton | ‼NULL‼ | ‼NULL‼
(3 rows)

INNER JOIN person ON person_id = person.id;
id | name | address | phone | email
----+------------+-----------------------------------+--------------+-----------------
2 | Joe Schmoe | 123 Credibility Street, Frogville | 987-765-5432 | joe (AT) example (DOT) org
(1 row)
=====

--
\ “Few things are harder to put up with than the annoyance of a |
`\ good example.� —Mark Twain, _Pudd'n'head Wilson_ |
_o__) |
Ben Finney

On Feb 10, 5:59Â*pm, Ben Finney <bignose+hates-s... (AT) benfinney (DOT) id.au>
wrote:

Thanks that makes sense. I can use triggers to enforce the
constraints.

I am pretty new to databases and have been kind of thrown into this. I
normalized the current DB as much as I could, so now I don't really
have a flat "prospect" table or "client" table. I've split this up
into many tables, for example a table for addresses that relates to
prospects such that a prospect can have many addresses, then another
that table that identifies a shipping address with a prospect ID, etc.
So, there are many tables to check for many fields.

I'll have to practice using triggers and see if I can make my way
through it.
It sounds like a good solution.