Post reply

Refactoring an Entity Attribute Value schema

  • February 7, 2022 at 9:33 am

    #3981062

    Apologies in advance, as I know this has been discussed a million times already but I think our requirement is slightly different from the norm in that we have a well-defined, static set of attributes up front and our values are rows in a table. But feel free to ignore if you've had enough of this subject already!

    Our scenario is Notes.

    1. We have a fixed number of entities (each with their own table) such as Person, Service, Organisation, Contract, Invoice, etc.
    2. We also have a table for Notes - this has quite a few columns in order to support existing functionality.
    3. Each instance of an entity (e.g. a Person or a Service etc.) can have any number of Notes.
    4. Mercifully, each specific Note can only relate to one instance of an entity - a given Note can only be referenced by a single Person, Service, etc.

    Currently, we have implemented this via two columns on the Note table:

    • EntityID - the PK value of a row in the Person, Service, Organisation, etc. table.
    • ContentTypeID - 1 for a Person, 2 for a Service, 3 for an Organisation, etc.

    The main problem with this approach is the inability to establish a FK from EntityID to the Person, Service, Organisation, etc. tables. There might also be a performance implication too.

    The problem with an alternative is that we have over 30 tables in the database that need to have Notes. My understanding is that there are two options for refactoring:

    1. Add 30+ nullable columns to the Note table; PersonID, ServiceID, OrganisationID, etc. These could be sparse and we could add indexes for only the non-null values, plus FKs to the Person, Service etc. tables. But that's a lot of columns!
    2. Add an "Entity Note" table for all the entities needing a Note. e.g. Person_Note, Service_Note, Organisation_Note, etc. These would simply contain pairs of PersonID and NoteID values (or ServiceID and NoteID etc.) But that's a lot of tables!

    I'm assuming that the recommendation to "use XML" wouldn't work here because the "values" part of our existing EAV model isn't simply a value, such as 'red', 20/01/1935 or 27.55 for example - it's an entire row in a table.

    So I'm starting to think we should just stick with the existing EntityID / ContentTypeID approach and hold our noses whenever we come across it.

    But if you know of a better approach, I'd be very interested to hear it. Thanks.

  • February 7, 2022 at 10:18 am

    #3981077

    Your existing method is probably the most practical. I considered other options and came to the same conclusions as you. I suggest that you stay well away from the XML 'recommendation', by the way!

    If you wish to ensure no integrity violations, you could consider adding a suitable trigger to the Note table which contains the necessary lookup checks & prevents violations.

     

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply