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.