Last week I made a posting about "Common table or separate tables", got a few replies, made a follow-up posting with more details, only to discover that the last posting got lost. Now the webmaster's got it back on the list, but I take the liberty of posting it again to get it at the top of the list instead of nine days back were nobody looks. So here it is:

In a one table solution the problems Don point out are important to deal with. I'm aware of many of them already. I've taken care of some of them, but not all.

But maybe what Mike mentions (if I understand him correctly) is important. All the objects in my case have very similar relations to other, common entities - persons, places, events. E.g all types of objects can have persons as earlier owners, users, donators, etc.

And there is something else that I didn't mention in my simplified description of my case: The objects can form a *hierarchy*. And please forget ships and planes and cars for now; I just made that up because I needed a very simple example. The types of objects in question are various very broad classes of museum objects. One type of objects are artefacts of cultural and social history. Another type is objects of art, a third one books and other publications, and so on.

Some examples of hierarchical objects are:

- Complex objects, e.g. a car (!). The car is an objects but also consists of many parts that need to be regarded as objects in their own right. So both the car and each part need their own records in the database.

- Groups or sets of objects that are separate but belong together, e.g. a suite of furniture or a cup and saucer. Both the single objects and the groups may need to have their own records in the database. Another example is a permanent exhibition at a museum which shows the study of a well known person. The study contains many single objects, but the objects may have so many things in common (e.g. acquired and managed as one unit, etc) that there is good reason to have them as a group with its own record.

Hierarchies can have an arbitrary number of levels, and in principle the same fields are needed on every level. So the only sensible solution is to have all levels in one table.

But I think the application interface to some extent needs to be aware of levels. I'm not sure if that means I need to have different windows for different (kinds of) levels, but certainly the windows must present different options depending on the level.

So is that similar to be aware of object type? If I have different types of object in one table the interface needs to be aware of that. Perhaps I need the same kind of programming when I have different *levels* in one tables as when I have different *object* *types* in one table?

One more thing: The hierarchical structure may cross the boundaries between object types. A group may contain objects of different types. So the study in my example above may contain both artefacts, art and books.

Does all this change your opinions? Does it provoke some new thoughts?

- Oystein -

View this thread on the forum...