Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011

    Red face EAV/Multiple Joins/Path to Insanity

    I have the following situation:

    - id [pk]
    - name

    - id [pk]
    - name

    - source_id [pk, fk -]
    - table_id [pk, fk -]

    - id [pk]

    - table_id [pk, fk -]
    - row_id [pk, fk -]

    - id [pk]
    - name

    table_row_field (EAV)
    - table_id [pk, fk -]
    - field_id [pk, fk -]
    - value

    - source (m2m) table
    - table (m2m) row
    - row (m2m) field

    My question is, how in god's name can I keep track of all this stuff!?

    I'm fine querying the data (and returning to a web UI in json) however when the user creates a new row in a table, I need to check (and insert, update if neccessary) basically all of these tables depending on if there are new fields added, new tables, new sources in addition to the data values in the cells themselves.

    I guess one solution is just to lose this model and have a lot of redundancy but just have one table to hold rows (and don't split out table and field into separate tables). This feels inefficient though and because I have unknown fields coming back the EAV model seemed appropriate.

    Is there a better way to work with the data model above and if not, how should I go about taming it for inserts and updates?

  2. #2
    Join Date
    Jul 2012

    Here's a Data Model for you


    I have created an E-A-V Data Model on my Database Answers that might give you some ideas :-
    Data Model for Entity-Attribute Values

    I look forward to hearing how you get on ;-0)


Tags for this Thread

Posting Permissions

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