Two attributes multiple Entities

  • Hello.

    I've been scouring the web but can't quite find the answer i'm looking for and was wondering if someone here might be able to point me in the right direction.

    I'm currently designing a Conceptual ERD model and have a question regarding two attributes Primary address and Registered address.

    Currently I have a supertype > Subtype relationship set up with Organisation being the super type and charity, company and trust being the subtypes. The organisation Super type contains the attribute PRIMARY ADDRESS which all three subtypes’ inherit.

    However only charity and company require the attribute Registered address (not the subtype Trust). I’ve had a few ideas on how to best model this i.e. add registered address to Organisation for all three subtypes to inherit but allow nulls so when Organisation and Trust are joined there will simply be null values...although I seem to think this breaks first normal form.

    Or should I create a new Address entity with two columns Primary address and Registered address which then links to the Supertype organisation. (during normalisation I would probably then break that out to Address and Address Type)

    Or should I leave primary address in the super type organisation and simply repeat the attribute registered address in company and charity.

    Or finally should I leave Primary address in the super type and create and new Entity AlternativeAdress which only contains registered address and simply link that to just Company and charity?

    If anyone has any ideas it would be very much appreciated.

    Many thanks in advance

    Alternatively I could add another supertype level under organisation of Registered Address and Non Registered Address

  • FairFunk (7/16/2010)


    Currently I have a supertype > Subtype relationship set up with Organisation being the super type and charity, company and trust being the subtypes. The organisation Super type contains the attribute PRIMARY ADDRESS which all three subtypes’ inherit.

    However only charity and company require the attribute Registered address (not the subtype Trust).

    As I see it Subtypes are created to track differences in between different flavors of the Supertype e.g. different attributes are needed in different subtypes of the same supertype.

    Following that line of thinking I would include "primary address" in the supertype - it will be inherited by the three subtypes - then track the differences in the subtypes, in this case I'll include the attribute "registered address" in the Charity and Company subtypes.

    Hope this helps.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Paul many thanks for the advice.

    How ever what would happen further down the line when it comes to implementing the design and if I decide not to roll up the subtypes to the supertype. That would leave me with 4 tables (organisation, charity, company and trust) and the same column "registered address" defined in two separate tables.

    Any other thoughts would be very much appreciated as I'm sure i am just missing some basic.

  • FairFunk (7/16/2010)


    How ever what would happen further down the line when it comes to implementing the design and if I decide not to roll up the subtypes to the supertype. That would leave me with 4 tables (organisation, charity, company and trust) and the same column "registered address" defined in two separate tables.

    Physical implementation is a different animal 🙂

    I would probably implement this at the Supertype level, meaning I'll create a single ORGANIZATIONS table to describe Companies, Charities and Trusts. This table will include both primary_address and registered_address columns.

    So... where are the subtypes? I'll include an organization_type column in ORGANIZATIONS table to document what kind of organization is being described in each row.

    Does it makes sense?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • Thats excellent advice, many thanks.

    Now I know that I can include the same attribute in multiple subtypes of the same supertype my conceptual ERD is going to look a lot cleaner.

    Also I think the OrganisationType column would work well further down the line, its definitely something I'll think about.

    Got to love that good fun learning curve! 🙂

  • Thank you for your kind words - glad to help.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • In a situation like this, once I find that one address is not enough, I allow for 1 to many by adding another table. In this case, it is something like ORGANIZATION_ADDRESS. This table just has columns like organization_id and address_id and type. Type can just be a string like 'primary' and 'registered'. You could add another table ADDRESS_TYPE and then have a type_id rather than a type, but this usually seems a bit over the top.

  • Hopefully its not a problem me linking to another website but I found

    http://stackoverflow.com/questions/307027

    very useful in my final solution.

    Institutional knowledge and all that.....

Viewing 8 posts - 1 through 7 (of 7 total)

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