Optimal design, large database
Posted by: Vincent Marshall
Date: June 29, 2007 12:24PM

We're looking for ways to optimize the design of a database for a large application. There'll be some 3-11 million records and more than 200 attributes.

Our developer has proposed a simple data structure that could be represented as follows:


---------- | Attribute 1 | Attribute 2 | Attribute 3 | Attribute 4
Entity A | True ------- | Blue ------ | 200000 --- | ------------
Entity 2 | False ------ | ------------- | 19 ---------- | 340056 -

Our trouble is that this model doesn't allow for the easy addition of attributes for when we need to extend the database.

Another problem we see is that we have entities which don't possess certain attributes, such as Entity 2 not having an Attribute 2. So we end up with blank, wasted fields as shown. Note that on average our Entities will only possess a small portion of all available attributes, I'd estimate 10-20%.

We would like to propose an alternative along the following lines.

We would like to use an Attibute table that will list all available attributes that can be assigned to the entities. A new attribute would therefore only require a new record in the Attribute table:

---- | Attribute name | Data type
A0 | foo -------------- | Boolean
A1 | bar -------------- | String
A2 | baz -------------- | Integer


Then we'd have a Value table that stores the attribute values for each entity:

---- | Entity ID | Attribute ID | Value
V0 | E0 ------- | A0 ---------- | 0
V1 | E0 ------- | A1 ---------- | Blue
V2 | E0 ------- | A2 ---------- | 42
V3 | E1 ------- | A0 ---------- | 1
V4 | E1 ------- | A2 ---------- | 83

What would be the downside of this alternate design? Will our application take a performance hit for the multiple tables? Or would the fact that we've efficiently used the tables make up for any degradation in performance.

Thank you for any guidance.

- VM

Options: ReplyQuote


Subject
Written By
Posted
Optimal design, large database
June 29, 2007 12:24PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.