Database tables for entries of another table?

Question

I'm designing a new revision of my Java application (using an embedded H2 database) around a redesign of the way I'll be handling my data. Here's how I have it planned:

• Entries table-
  • Entry ID
  • Entry name
• Properties table-
  • Property ID
  • Property name
• (Individual property) value table-
  • Value ID
  • Entry ID
  • (Value columns...)
• (Individual entry) value table-
  • Property name
  • (Individual property) value ID

Each entry can have multiple properties (including multiple properties of the same type). Each property has its own way of storing its values. I need to look up all properties defined for a given entry, and maybe all entries for each given property.

Is this a good way to do it?

Edit: I'm not sure I explained it well...

Answer 1

If I understand you correctly, I would use intersection or junction tables instead of what you described.

So you can create a query to get you all the Properties per Entery, or all the Enteries per Property.

Answer 2

In my opinion, that's a very bad way to model data, but this is a very ivory-tower way of looking at the situation as I haven't had to use this model in practice. By the way, it's called the "Entity-Attribute-Value" approach. And the reason I dislike it is because it's very un-schema-like in that most SQL functionality has to be replicated in some way.

There's definitely a time and a place for it (like if you intend to model many objects that have disparate models) or that have schemas that change frequently. But I personally think it's terrible.

Answer 3

I agree with Unknown Google. This is also called the Inner-platform effect.