Multiple one-to-one relations

Question

I'm trying to design database relations for archaeological places in a country. There are around 9000 places registered. An archaeological place can have multiple types. Types will be like Cave, Mound, Necropolis...There are around 100 different types. For example an archaeological place can be a necropolis and a mound at the same time. For different types, different attributes will be valid. For example, a mound type will have attributes such as diameter, direction etc...Another type, for example, a cave will have attributes such as depth etc...

Common attributes for archaeological places are id, name and location. I thought about creating a table with these common attributes and link to other tables. Each table linked to will hold information specific to a certain type. The problem is there will be multiple one-to-one relations. A particular id, name or location entry may point to tables type1, type2 etc.. but it cannot point to two or more different entries in the same table. Number of different tables an entry points to differs. It can point to one or 10 or more. So it is not balanced. If I create 100 one-to-one relations and most of them will be empty for most of the entries.

Can there be a better design for representing such data? Better in terms of compactness... I can simply create a huge table with all the attributes as columns but some of the attributes will simply mean nothing for some types. Are there different ways to structure multiple one-to-one relations?

Edit: Here is an example: Lets say that there are 3 types

A, B, C

Attributes for these types are as follows:

A: x, y, z

B: z, k

C: x, m

A sample table will look like as follows:

Entry   Location    Type    x   y   z   k   m
entry1  location1   A       o   o   o       
entry2  location1   B               o   o
entry3  location2   B               o   o
entry4  location4   C       o               o

"o" represents data.

As you can see, entry1 and entry2 share the same location but their type is different. I started with creating a table for locations(plus names which are unique to locations). Then I will have seperate tables for each type. Entries in location table will have one-to-one connections to different type tables. I couldn't find a better way to organize this. At the end, I will create an app which shows archaeological places nearby. Users will be able to select and filter certain types.

Answer 1

Your problem can be solved with the following set of tables:

A table location, with attribute id, and attributes common to all location (like position, etc.)

A table for each type of location, with all the attributes specific with that table, and an attribute location_id which is a foreign key for the table location.

Then you could consider of adding, if necessary, an attribute to the table location that represent in a compact form all the types associated to that location (for instance an array if you use PostgreSQL, or something similar).

Answer 2

The following schema is what I came up as a fully denormalised set of tables. There are pros (e.g. flexibility) and cons (e.g. user interface complexity) to this approach, but you may find it useful, or it may shed some insights:

create table arch_type_att_types (
  attribute_type_id serial not null primary key,
  attribute_type varchar(100) not null, -- e.g. diameter
  measurement varchar(100) null, -- e.g. metres
  notes text null
  );  

create table arch_types (
  arch_type_id serial not null primary key,
  arch_type varchar(100) not null, -- e.g. Cave
  notes text null
  );

create table arch_type_attributes ( -- which attributes each type has
  arch_type_id int not null references arch_types,
  attribute_type_id int not null references arch_type_att_types,
  notes text null,
  primary key (arch_type_id, attribute_type_id)
  );

create table arch_places ( -- the actual list of archaeological places
  arch_place_id serial not null primary key,
  name varchar(200) not null,
  notes text null
  );

create table arch_place_types ( -- which types an archaeological place qualifies for
  arch_place_id int not null references arch_places,
  arch_type_id int not null references arch_types,
  notes text null,
  primary key (arch_place_id, arch_type_id)
  );

-- all the attributes of an archaeological place,
-- restricted to the types it qualifies for
-- (in text format, would need casting to specifics and according to
--  the corresponding measurement of each attribute type)
create table arch_place_attributes (
  arch_place_id int not null references arch_places,
  arch_type_id int not null,
  attribute_type_id int not null references arch_type_att_types,
  value varchar(2000) not null,
  notes varchar(2000) not null,
  primary key (arch_placeid, arch_type_id, attribute_type_id),
  foreign key (arch_place_id, arch_type_id) references arch_types (arch_place_id, arch_type_id),
  foreign key (arch_type_id, attribute_type_id) references arch_type_attributes (arch_type_id, attribute_type_id)
  );

The schema is for PostgreSQL, you would use the equivalent of serial for the auto_incremental implementation of your RDBMS of choice.