I think you've hit the nail on the head. The only part I don't like
about this approach is that sub.type_id will always contain the same
value. I will evaluate this scheme in detail as I implement it and
report back if I find any anomalies.

--
Bernd


Lennart wrote:

I spoke to soon. One problem remains in this design: I could have a
record in the supertype table that is not referenced by any of the
records in the subtype tables.

What can I do in this situation? Formulating it in a different manner:
Suppose I have two entities, A and B. An instance of A implies and
instance of B and vice versa. How would I express this contraint with
the relational model?

eKo1 wrote:

eKo1 wrote:
True

As far as sql (supported by db2) goes I dont see how this can be
implemented. In the relational model I dont know, assertions at db
level?


/Lennart

Lennart wrote:
You know, typically newsgroups (like this one) have just one table -
something like this:

messages(message_id*,parent_id,message,date_posted )

new topics, then, are simply messages in which the parent_id is NULL

maybe it's an inappropriate model for your db but I thought I'd mention
it just in case.

eKo1 wrote:
I still think you are mixing physical model with logical model too
soon. You keep talking about ID values and type fields, but you have
never defined exactly what constitutes a LOCATION. What attributes does
it have? What attributes does a department have that make it a location
but make it different from a sector? What attributes make them the
same so that you pick it as a location?

Forget the IDs and types and define your attributes. So far all you
have mentioned is that each has a name. Your problem stems from this
basic issue. Before figuring out whether to fit things in square or
round holes, you need to determine whether they are even pegs instead
of chairs and apples.

HTH,
ed

eKo1 wrote:
check constraint you can apply on the table which will reject duff data
even before it has to check the foreign key constraint.

aware of), since you're going to have to insert into one of the tables
before you insert into the other, so there must be some period of time
when you cannot tie the relationship in both directions.

Even in those products which allow you to defer constraint checking
until the end of the transaction, they don't tend to have the only type
of constraint you'd be able to use in this case - a database
constraint. A foreign key constraint is insufficient, since the foreign
table could be one of many different tables.

If I were you, I'd try re-reading some of Eds replies and try to
understand what he's getting at.

Damien

Ed Prochak wrote:
Well, as I said, I have three entities: department, municipality and
sector. These are political divisions. Each department has one or more
municipalities and each municipality has one or more sectors. A
location can reference an instance of one of these political divisions.

Each political division has a name so department, municipality and
sector have name as an attribute.

Is that a good enough description?

eKo1 wrote:
Actually, no. You still keep mentioning only ONE attribute, name. There
has to be more.

Also at the logical level you must ask questions like
What is a location? How is it used? (say it in a plain english
sentence)
Note that nothing in these kinds of questions mentions anything about
an ID.

For example, if I was creating a scheduling application and it could
refer to locations such as offices, conference rooms and auditoriums.
Each location has a name (Ed's office, Conf Room B, the Famous Person
Auditorium), but it also has attributes like seating capacity, and
physical address (e.g. Conf Room B is first floor, West Building, 123
This Street, Oak, Ohio)

Now your Location entity is less about physical position (I think), but
it also seems to have the added information that the locations are
hierarchically related. That doesn't affect the location type though.
But it should give you more attributes and relations to consider.

Note your description above also mentions the existence of another
entity: Political Division. What are its attributes? What makes a
sector a political division?

Have you tried drawing an ERD? That would be your best step before
thinking about tables.
Ed

Ed Prochak wrote:
Why? For my application, I only care about the name. I don't need any
other information.

How is it used? Well, I have another entity called "telephone number
range". Each instance of this entity must have a location attribute
that specifies where the range of telephone numbers operate. For
example, the range 123000 - 123999 are telephone numbers in the
department of Foo. The location attribute, therefore, contains the
department Foo. You see, a telephone number range may be assigned
department-wise, municipality-wise or sector-wise.

Yes, the hierarchy department->municipality->sector is not expressed in
the location entity.

The only attribute this would have is name. There would be three
instances of such an entity: one named Department, one named
Municipality and one named Sector. They are political divisions because
that is the way things are divided here politically.

I did draw one (in my mind since it is simple) and I noticed that since
the attribute name is common to all subtypes, then that attribute
should be in the supertype, i.e. location. But then Department won't
have any attributes. Municipality would have on attribute, namely
department and sector would also have one attribute, namely
municipality.