superclass in geographical database?

[jonz]

Hi,

I'm in the process of designing a database for certain geographical
areas. This is will be one of the first databases I create. I've identified
some entities with different sets of attributes:

-Danger_area
-Transportation_area
-School_area
-Playing_area


There are a few geographical attributes that are common for all of these
entities.

I'm thinking about using a superclass (General_area) to hold approximately
these attributes:

-area_id {PK} (relates the entity to a map I've made)
-state
-county
-description


The relation between the superclass and the subclasses is going to be
{Mandatory, Or}. My initial reason for placing these attributes in a
superclass was probably to reduce the number of attributes in the other
classes(this is probably not a very good argument?). After having done this,
I thought that it also seemed logical to isolate the geographical attributes
from other attributes.

The database isn't going to be huge. I think there'll be less than 1000
areas (tuples)in total. Do you think it is reasonable to use a superclass in
this situation, or is it better to include the geograpical attributes in each
of the specific area entities? Is it straightforward to implement
superclasses in Access?

 

[Fred]

Databases pervasively implement and store superclasses and tuples, and so, in
the context of your question, I would view those terms as as a synonym for
"database" and not a substitute for specific definitions and relationships of
the entities of the information/real world process that you want to
database.

 

[Fred]

So one of your first questions is, what are the entities that you are going
to database. My first guess is "areas", which would mean that
"danger"/"transportation"/"school"/"playing" would be just an attribute of an
area.

Barring complications (like an "area" spanning multiple counties or states)
those would also be attributes of areas. So, in short, for what you've
described so far, one table, possibly with some lookup tables for counties,
states etc. would probably be best.

 

[jonz]

I've observed that the word "superclass" seems to be little used in this
discussion group. This is the definition from ("Database Systems" 3rd ed. by
Connolly/Begg, 2000):

Superclass: An entity type that includes one or more distinct subgroupings
of its occurences, which require to be represented in a data model.


I'm not 100 % certain that I grasped what you tried to explain in the first
reply. For now, I'll just assume that you meant that the superclass and all
the tuples in it is equal to the entire database or something like that (I
could probably design it exactly like that, but I don't think that would be a
good design).

I'm going to go a little bit more into detail about the different areas.
Let's view them as separate entities for now, and consider to generalize them
later.

Playing_area
---------------
playing_area_id {PK} (autonumber)
state
county
description (f.ex. soccer field)

data_source
data_quality
boundaries (exact or approximate)
buffer (For the moment, the values are restricted to "yes" or "no")

main_season (summer or winter)
number_of_users
frequency_of_usage


Playing_area is related to the entity "Activity" (1:*), used to describe the
activities performed in the playing area).


Danger_area
---------------

danger_area_id {PK} (autonumber)
state
county
description (f.ex. the name of a dangerous river)

data_source
data_quality
boundaries

With my choice of mapping for danger areas, there would never be a buffer.
The last three attributes of Playing_area is related to its usage, and has no
value for any danger area.

Danger_area is related to the entity Danger (1:*), used to describe the
dangers in that area.

Let's focus on these two area types now. I could use the generalized entity
Area:

Area
-----

area_id {PK}
area_type (danger or playing)
state
county
description
data_source
data_quality
boundaries
buffer ("no" if area_type = danger)
main_season ('null' if area_type = danger)
number_of_users ('null' if area_type = danger)
frequency_of_use ('null' if area_type = danger)


I'm thinking that there might be some trouble when relating "Activity" and
"Danger" to "Area", but maybe I'm wrong.

Since I've opted not to establish any kind of relationship between
"Danger_area" and "Playing_area", I guess I could actually make two separate
databases instead of including both in the same database. The geographical
relations between the areas is apparent from the map. Do you think this would
be the best option?

 

[Fred]

Regarding my first comment, long story shot I was basically saying that I
felt that the term is not very useful for the specific communications needed
for this thread, and could even do harm if it displaced them.

One other Access terminology note - in Access, a "database" is a container,
that holds lots of stuff including tables, queries, forms, reports etc.

I think that you have a pretty good handle on this and would bet that
whatever you come up with would be good. In this thread there are three
basic design approaches:

#1 4 individual tables for your 4 area types

#2 One big table containing all of these entity types, with a field to
tell them apart, and rules such as you describe to deal with non-applicable
fields

#3 One area table with fields for those attributes which are in common with
all area types, and then 4 individual area type tables with fields for
attibutes which are not in common with all area types.

A decision process driven only by normalizaiton goals / rules would pick #3.
My own choice would be influenced by:

Q1 Will you be combining area types into single lists very often? If so,
you'll need fancier querying skills to use #3

Q3 How different are the attributes for the other two area types
(Transportation and School). If they are really different, then idea #2 is
going to get pretty messy.

If I were doing it:

If Q1 = yes, and Q3 = not very different, then I'd do #2

If Q1 = no. and Q3 = either answer, then I'd do #3

If Q1 = yes and Q3 = very different I'd go with #3


Hope that helps a little.

 

[jonz]

Thanks for the input, Fred. My next step is to implement this in Access. I
might make another post about that step later.