I need help designing my oo database to handle multiple kind-of relations

Question

i'm currently creating a database program in java and i am a bit stuck with the design. it's pretty straight-forward until i hit the part where there are more than 40 different kinds of activities that can range from from book-selling to tutorials to radio/tv show appearances. each activity type has a different set of properties, such as documents needed, name of the kind of activity, etc.

i've considered just hard coding each activity. i've also considered generalizing the types of activities into subclasses such as attendance-based activites and purchase-based activites, but it limits the extensibility of the system. and i have to consider the classes of objects as well. would have to make 40 different classes that all extend activity? i considered creating an activity_type table as well but i do not know how i would implement the activity types in the java part.

my question is: how should i design my system?

Answer 1

Do you need to search/sort using all these extra properties, i.e. do they need to be SQL fields? If not you can have your core properties as indexed fields, and the other fields in a blob (e.g. an XML document) which is stored in one field of each SQL table.

Answer 2

Well one other approach you could consider is to use metadata for your objects. You could have an activity table with all the base information. Then have an activityattribute table that stores all the metadata for each of your objects. This makes it very easy to create new objects with minimal amount of code. But this definitely makes it harder for querying.

When creating you just apply templates that contain all the attributes that should be applied for the object you are creating.

Answer 3

Here's one viewpoint of hard-coding the activities vs storing them in the database:

If you hard-code the activities in your code, you can't add (or delete or alter) an activity without recompiling and redeploying your code. But while coding, you know all of the possible activities, so your code can handle them all.

If you store the activities in the database, you can easily add, delete or alter an activity. And you could allow users (perhaps only users with certain roles) to make changes to activities. But your code may not necessarily handle a new activity properly.

So, I would base my choice on whether the code can handle changes in the activity list. If it can -- for example, the activities are only displayed, or it's just CRUD with activity choices in dropdownlists -- then I would lean toward the database approach. That is because you can change the activities in the database without recompiling your code.

Answer 4

You might want to read about the Entity-attribute-value model.

Answer 5

Consider this model

Type-of-Activity 1 <----> * Type-of-Things-Needed
  1                           1
  |                           |
  *                           *
Activity         1 <----> * Things-Needed

You would need to ensure that only Things-Needed can be added to an Activity that are of the Type-of-Things-Needed associated with the Activity's Type-of-Activity.

Answer 6

There is a pattern available. Take a look at this question/answer; describes the observation pattern. It uses five tables and can be implemented in a "standard" RDBMS. An entity can have any number of custom properties (observations).