InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 14:47:30 GMT20http://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxHopefully its not a problem me linking to another website but I foundhttp://stackoverflow.com/questions/307027very useful in my final solution. Institutional knowledge and all that.....Tue, 10 Aug 2010 04:55:25 GMTFairFunkhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxIn 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.Tue, 03 Aug 2010 13:31:36 GMTrobertfolkertshttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxThank you for your kind words - glad to help.Fri, 16 Jul 2010 11:05:41 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxThats 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! :)Fri, 16 Jul 2010 10:56:17 GMTFairFunkhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspx[quote][b]FairFunk (7/16/2010)[/b][hr]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. [/quote]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?Fri, 16 Jul 2010 10:40:09 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxPaul 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.Fri, 16 Jul 2010 10:33:54 GMTFairFunkhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspx[quote][b]FairFunk (7/16/2010)[/b][hr]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).[/quote]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.Fri, 16 Jul 2010 10:24:05 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic953749-373-1.aspxHello. 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 advanceAlternatively I could add another supertype level under organisation of Registered Address and Non Registered AddressFri, 16 Jul 2010 04:57:49 GMTFairFunk