Database Design Question

[sleoni]

Hi, I'm currently working on a large database that will hold Astronomer's observations, while using telescopes. On of the items on the form is the Object that was observed.

In the database I also need to store basic information on each object (static information - not related to the actual observation)- the problem is that different Object Types have different fields.

For example, here are some of the Object Types: Planet, Comet, Star, Standard, Asteroid. Each of these type of Object can have different set of attributes.

Currently, my incomplete solution is to creat a separate table for each Object type with the appropriate fields for each type.

So for example, i will have a "Comets" table with the fields "a", "b", "c", "Planets" table with the fields "x", "y", "z", etc'. In each table I will list all the object that corespond to the appropriate table.
However this solution is not complete, because the Astronomer will have to choose a single object on their observation log sheet.
How can I combine all of these tables into one list?

(I'm currently in the design stage so it is not a problem to make modification to the tables.)

Please let me know if you have any idea on how to solve this problem. Thanx in advance.

- Sivan

 

[martynh]

There isn't a simple answer, and in truth ask 20 designers and you'll probably come up with about 15 different answers. Which is best for you is probably more to do with how you are going access and use the data afterwards. I'd suggest that the two extremes are a table per Object Type (as you have), and a single table with a long character field (BLOB/CLOB?) holding an XML structure with a different DTD for each object type.

The latter of these designs is the easiest in terms of design and set up, but it assumes that you will not be doing much (if any) adhoc querying of the data. An insurance company I worked for used this approach very successfully to hold data from a web-based quotation system where the quotes could be for a number of different policy types, each with their own attributes. However, the data was read by applications and farmed off to policy type specific systems.

A 'middle ground' approach is to create a central table that has just the attributes common to all object types (there must be a few common attributes, I would have thought). Then create a separate table for each different object type with their unique attributes, but using the same key fields. I've seen this refered to as a 'vertical split' in database design terms. If you have some attributes that are common to most but not all objects, you can add these to the central table and make them nullable. Finally, create a view for each object type spanning the central table and its specific table so users can easily see the data for each object type.

As I said at the start though, it very much depends on how the data is getting into the database, and how its is being extracted/used.