SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Two attributes multiple Entities


Two attributes multiple Entities

Author
Message
FairFunk
FairFunk
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1650
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
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14221 Visits: 4639
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.
FairFunk
FairFunk
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1650
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14221 Visits: 4639
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.
FairFunk
FairFunk
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1650
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! Smile
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14221 Visits: 4639
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.
robertfolkerts
robertfolkerts
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 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.
FairFunk
FairFunk
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 1650
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.....
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search