Results 1 to 6 of 6
  1. #1
    Join Date
    May 2010
    Posts
    10

    Problem With a database design

    I am a dba and a database design was thrown over the wall at us. The designer maintains this design is in 3rd normal form. I know this isn't true the database contains structures that follow a metadata model. Can anyone point me in the direction of some documentation to de-bunk the persecption that this model is a normalized model. I do understand how the model should look. Please note The example I gave is not the exact design.

    Assets - Buildings,Cars,Airplanes
    Items - Chairs, Seats, Cushion
    Assets
    -----------
    AssetId pk
    Name
    AssetType

    Items
    -------------
    ItemId pk
    Name
    ItemType

    Parameters
    -----------------
    ParameterId pk
    ParameterName
    ParameterType --- for this example the type (Item,Asset)
    ParameterUnitMeasure
    ParameterDatatype

    ItemParameters
    -------------------
    ItemId pk
    ParameterId pk
    Value

    AssetParameters
    --------------------
    AssetId pk
    ParameterId pk
    Value

  2. #2
    Join Date
    Feb 2012
    Posts
    76
    It looks to be in at least 3NF, but normalization isn't the issue, it's lack of design. This is a variation of the entity-attribute-value model. EAV is a simple schema, hence its appeal. However, it discards much of the convenience, robustness and efficiency of the dbms. It discards appropriate data types for a general purpose varchar (or similar) 'value' field. This eliminates validation and optimization by the dbms and makes indices, at the very least, inefficient. Constraints become difficult, if not impossible, to define. Complex queries often need to join this monster value table to itself multiple times to reconstruct what should have been the design of database. If not self-joins, then possibly a high number of small round trips which increases the load on the infrastructure.

    It's possible for a program to dynamically alter tables according to need, by issuing DDL like ALTER TABLE instructions to the dbms. Unlike EAV, this technique keeps strict bounds on data integrity and you're forced to play by the rules. Also unlike EAV, your data will be well-defined.

  3. #3
    Join Date
    May 2010
    Posts
    10
    Thanks I found some documentation on the EAV model. Is it considered a relational model?

  4. #4
    Join Date
    Feb 2012
    Posts
    76
    It doesn't model the business domain at all, unless one's business domain is labels. It's not relational either (in the mathematical sense), but then again, neither are SQL dbms's in general. However, you should focus on pragmatic issues rather than purity.
    Last edited by reaanb; 03-05-12 at 16:11. Reason: Expanded my answer

  5. #5
    Join Date
    May 2010
    Posts
    10

    Thanks

    Thank you so much. My problem is that I am the physical dba. It falls to me to optimize this data. Based on it's design there isn't much I can do. Let me tell you performance is a dog. To do the simplest query involves a 10 level join, with 5 of the joins on the same table.

  6. #6
    Join Date
    Feb 2012
    Posts
    76
    If you're looking for an argument based on normal forms, here it is:

    As far as the business model is concerned, the ItemParameters and AssetParameters tables aren't even in first normal form, since the value columns don't have consistent domains.

    Throw it back over the wall.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •