Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Two attributes multiple Entities Expand / Collapse
Author
Message
Posted Friday, July 16, 2010 4:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:37 AM
Points: 47, Visits: 1,425
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
Post #953749
Posted Friday, July 16, 2010 10:24 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #953994
Posted Friday, July 16, 2010 10:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:37 AM
Points: 47, Visits: 1,425
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.
Post #954002
Posted Friday, July 16, 2010 10:40 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #954005
Posted Friday, July 16, 2010 10:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:37 AM
Points: 47, Visits: 1,425
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! :)
Post #954017
Posted Friday, July 16, 2010 11:05 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #954028
Posted Tuesday, August 3, 2010 1:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 28, 2013 2:47 PM
Points: 11, Visits: 90
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.
Post #963097
Posted Tuesday, August 10, 2010 4:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 19, 2014 2:37 AM
Points: 47, Visits: 1,425
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.....

Post #966539
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse