Conceptual ERD Multi-table many to many, or possibly recursive?
It is great that you are taking the time to understand, classify and model the data you are dealing with since, from my personal experiencie, all this makes the whole development process easier and very flexible for future changes. And I am quite sure that you are also aware of this already.
Preliminary data model and assumed business rules
I defined a list of business rules that I have assumed after reading your question and examining closely your diagrams, in order to describe my understanging of your specifications. After defining such list, I derived an IDEF1X[1] data model that I decided to upload as a .PDF document in an external platform (Dropbox), since due to its format this data model does not fit well in an embedded image. These two instruments are going to be useful as references for some important points that I enumerate below in the section entitled Aspects to resolve in order to keep moving forward.
First, here is the…
- Organizations and Profiles Preliminary Data Model.
Since it is only that, preliminary, consider it as an means helping us to acomplish the desired final data model.
Assumed business rules
Said preliminary data model was derived from a collection of business rules (inferred from your question) that I will enumerate as follows:
Organizations and profiles
Note that Profile
is currently understood as a synonym for Person
.
- An
Organization
is a friend of one-to-manyProfiles
. - An
Organization
is a friend of one-to-manyOrganizations
. - An
Organization
is a member of one-to-manyOrganizations
. - A
Profile
is a member of one-to-manyOrganizations
. - A
Profile
is a friend of one-to-manyProfiles
. - A
Profile
is a member of one-to-manyProfiles
.
Locations and addresses
An
Organization
owns one-to-manyLocations
.A
Location
is classified by one-to-manyLocationTypes
(only one at a given point in time).A
Location
may have one-to-manyAddresses
(onePhysical
, one forShipping
, one forBilling
, or one that serves all said purposes, or one that combines two purposes and another that serves only one of them).An
Address
may be kept by one-to-manyProfiles
or, put another way, aProfile
keeps one-to-manyAddresses
.A specific
Address
may be used by one-to-manyProfiles
(serving asPhysical
for oneProfile
, being used forBilling
by a different one, etc.). So, anAddress
works in a similar way forLocations
andProfiles
.- Thus, an individual
Address
may be, at the same time, of typePhysical
,Shipping
andBilling
.
- Thus, an individual
Locations and roles
- A
Location
opens one-to-manyRoles
. - A
Role
may be carried out in one-to-manyLocations
. - A
Profile
(once it has been set asMember
of anOrganization
) may carry out one-to-manyRoles
, in one-to-manyLocations
(but only one specificRole
in eachLocation
at a particular point in time, i.e., never two or moreRoles
at the same time).
Aspects to resolve in order to keep moving forward
In order to keep advancing in the resolution of your data model, here is a list of relevant points that, once we work them out, are going to help us to reach this goal:
I have assumed that the term
Profile
in your context has a similar (or the same) meaning as that ofPerson
, but it could be a bit different. In this way, would you say that, in your scenario, the entitiesOrganization
andPerson
are subtypes ofProfile
?Can a
Profile
(orPerson
) own one-to-manyEmailAddresses
, or is aProfile
(orPerson
) fixed to exactly oneEmailAddress
?Would you like to provide the possibility for an
Organization
to be contacted viaTelephone
andEmail
, or you want to restrict that to be possible only for aProfile
(orPerson
)?I assume that a
Location
is fixed to exactly oneAddress
of the typePhysical
, is this correct?Is it possible for a
Location
to be shared by one-to-many differentOrganizations
or, otherwise, aLocation
can be owned by only oneOrganization
?You have stated via comments that the fact of being a
Member
and aFriend
is the same. As you can see in my proposed preliminary data model, I followed you original specifications and depicted all the possible combinations of membership and friendship betweenOrganization
andProfile
(orPerson
) in different entities since I think that it can be helpful in the effort of defining the best possible structure for that part of your scenario. In this sense:- I assume that the the statement
an Organization is a Member of another Organization
has different effects than the statementa Profile (or Person) is a Member of an Organization
regards the entity calledLocation
. - As you can see in the data model, I think that the
Role
ofOwner
is only valid for anOrganization
and, to me, the validRoles
for aProfile
(orPerson
), inside aLocation
areAdmin
andMember
. What do you think about all this? Since you are in direct contact with the business rules that apply to your situation, you need to tell me if my assumptions are correct.
- I assume that the the statement
Can a
Profile
(orPerson
) play differentRoles
inside the sameLocation
? i.e., can aPerson
be, at the same time, theAdmin
and also aMember
of the sameLocation
? What are the rules in this regard?I think that the same
Profile
(orPerson
) can play differentRoles
in differentLocations
. For instance: A specificProfile
(orPerson
) is the “Admin” inLocation
“1”, and this sameProfile
(orPerson
) is a “Member
” inLocation
“2”, at the same time. Am I right?Is it possible for a particular
Location
to have differentLocationTypes
at the same time, or is an individualLocation
fixed to hold exactly oneLocationType
?Does the attribute
Organization.Website
represent the website address of a particular organization, such as “dba.stackexchange.com”?If
Profile
“1” (understood asPerson
) is aMember
(orFriend
) ofProfile
“2”, is it possible forProfile
“1” to carry out aRole
in aLocation
owned byProfile
“2”? I consider that such scenarios are only valid for the relationships between anOrganization
and aMember
Person
so, what do you think?In a similar way, if
Organization
“1” is aMember
(orFriend
) ofOrganization
“2”, is it possible forOrganization
“1” to carry out aRole
in aLocation
owned byOrganization
“2”? Again, I think that this kind of scenarios are only valid for the relationships between anOrganization
and aMember
Person
, is this correct?In this regard —while I am writing this questions— I think that it would be reasonable to say that there are only three different kinds relationships involving
Organizations
andPersons
, and we can define:- (a) The relationship between an
Organization
and aPerson
as “Membership
”. - (b) The relationship between a
Person
and another differentPerson
as “Friendhip
”. - (c) We have yet to find a meaningful name in order to describe the relationship between an individual
Organization
and another differentOrganization
. - So, let me know what you think about (a), (b) and (c).
- (a) The relationship between an
Is it possible for an
Organization
to be aFriend
(or aMember
) of one-to-many differentOrganizations
at the same time? Or it is only possible for anOrganization
to have only a relationship with exclusively one differentOrganization?
Successive data model depicting the first advance
In attention to your responses and resolutions to the pending aspects that I have listed above, I have created the following…
- Organizations and Profiles Preliminary Data Model — First Advance.
Although I do not feel quite comfortable with it yet, this new data model expresses the following business rules:
- A
Profile
is either anOrganization
or aPerson
.[2] - A
Profile
may be the offering friend of one-to-manyFriendProfiles
, and aProfile
may be the accepting friend of one-to-manyFriendProfiles
.[3] - A
Location
may consist of one-to-manyLocations
.[4]
Answers to your subsequent specific comments
It's really interesting for me to note/compound the separation of concerns [e.g. LocationAddress and ProfileAddress] - for I obviously wanted to rush in and hold them all without the correct relations [funnily, It didn't feel right with my original ERD].
Yes, that is a good comparison, although I would not call it separation of concerns (which is, certainly, a fundamental principle in application programming and design), since this term commonly pertains to the application development stage and we currently find ourselves in the stage of understanding the data and designing its logical structure.
From my personal experience, I consider that this phase has to do with putting the significant things into their whole context, it has to do with seeing the associations that exist between the different entities that are of relevance in the particular scenario of interest, and then depicting these things in a data model. In the specific case on which you are commenting about, the Address
entity may have different kinds of connections with other entities, one with Profile
and a different one with Location
.
And, yes, when something does not feel right or natural, it may well be a sign that one needs to put more effort in order to understand the pertinent data. In this manner, the Address
entity is one of the things that I consider that needs more attention, since I think that the relationship between a Profile
and an Address
could be handled by means of the Location
entity (due to the fact that every Location
must have at least one physical Address
), therefore we could dismiss the ProfileAddress
associative entity depicted in the latest model, but you should continue analizing these points and let me know your ideas.
Also, is it common practice in IDEF1X to change PK/FK denotions in entities for better readability [e.g ProfileId - LocationOwnerProfileId]?
Yes, that is a very clever remark from you, since IDEF1X recommends the use of role names for denominating FOREIGN KEYS, in order to capture the meaning of such attributes in accordance to the entity in which it is being used. It is also worth noting that this is also strongly related to the concept of primary keys migration. As a matter of fact, the use of role names precedes IDEF1X, since it was originally presented by Dr. E. F. Codd in his 1970 seminal text. In this manner, one can clearly see the fidelity that the IDEF1X standard keeps towards the relational model.
I'd be intrigued to learn what you don't particularly like/feel it doesn't model, with/in the solution?
Besides the details already described above about the Address
entity, I am not sure if the Roles
carried out by a given Profile
in a particular Location
are equivalent for an Organization
or an Person
. From my perspective, a Person
first needs to be associated with an Organization
, and then this Organization
would appoint said Person
to perform a Role
in a particular Location
, but you know the scenario better, so this rules may be needless. In this regard, I am going to insist about the fact that it would be very helpful for me to know the contextual description or meaning that the future users of this data structure give to Organization
, Profile
, and Location
, but I understand that this may be considered confidential information, so this would be a limitation.
With the current structure, it seems like everyone (Organization
or Person
) can be related to anyone (again, Organization
or Person
) and can be/do anything (Role
) anywhere (Location
) but, perharps, this is preciselly what you and the users are expecting from this database, for which you will provide well defined constraints, of course. If this is the case, then we are almost providing a final solution. Since, naturally, your opinion is decisive in this situation, you should also analize this ideas and then let me know your conclusions so that we can take the final steps.
Feasible second advance
Unfortunately, the comunication stopped a few weeks ago, I guess because of work commitments that you must meet, which is completely reasonable. I would have been much more content if we had developed a more stable and robust model but, due to our previous interactions, I can assume that I have been able to point you in the right direction.
In addition to what has already been presented in this question and answer process, I consider that providing a new progression from the previous data models may be helpful for other seekers with a similar problem. So, I have created the…
Organizations and Profiles Preliminary Data Model — Second Advance
As can be seen in such data model, I have removed the many-to-many relationship that I have depicted in the preceding models between Profile
and Address
, since a given Profile
is already related to one-to-many Addresses
via its owned Locations
.
Another change that is illustrated in this new advance is the fact that it now includes the possibility that a given Location
can be owned by one-to-many Profiles
. Consequently, I have changed the Location
PRIMARY KEY (by dismissing the LocationOwnerProfileId
attribute) and then added an associative entity (many-to-many) that relates Profile
with Location
.
Notes
1. IDEF1X is a highly recommendable data modeling technique that was defined as a standard in december 1993 by the U.S. National Institute of Standards and Technology (NIST).
2. This is an ocurrence of a (super)type-subtype cluster. In case you are interested, here is an answer in which I deal in a more detailed manner with this kind of relationships.
3. An example of a many-to-many hierarchical relationship, and is very simliar to the structure that gave definitive solution to the “Parts Explotion Problem”. Such solution was, of course, introduced by Dr. Edgar Frank Codd in his 1970 enormously influential paper “A Relational Model of Data for Large Shared Data Banks”.
4. As such, this is an instance of a one-to-many (or many-to-one) hierarchical relationship.
I think you are trying to blend together concepts from object modeling and concepts from data modeling in a way that isn't helping you to clarify your own understanding of the problem. I hope I can clear the clutter a little without too much rambling.
The relational model, as such, does not support inheritance, never mind polymorphism. This means that a rather specialized design pattern has to be used when modelling a real life situation that is easily handled by inheritance and polymorphism in an object model. More on that special design pattern later.
When the ER model was first developed, it was supposed to be an implementation agnostic alternative to relational modeling. At first, it didn't have anything like inheritance either. But some time in the 1980s or 1990s, the model was extended to provide some of the same expressive capabilities that you get with inheritance. This was known as the "extended ER model", but for all practical purposes, the ER model of today includes EER features.
One EER feature goes by the name "generalization/specialization". You can search for and read up on this concept on the web. Gen-spec provides much the same expressive capability that classes and subclasses provide in an object model. However, Gen-spec does not deal with the issues surrounding relational table design for a gen-spec situation. More on that later.
In ER modeling, a relationship always involves the same entities. Therefore, the friend relationship between an organization and a profile is not the same thing as the friend relationship between a profile and another profile. The two relationships need different names. You'll just tie yourself up in knots if you don't follow this rule.
Either that, or you need to come up with a generalized entity of which Organizations, Profiles, and possibly Locations are all specializations. I don't understand your case well enough to help you with that.
Moving on, I notice that you are combining your relational model and your ER model together into a single model. Most seasoned database architects do this. But I advise you to keep the two models separate (although reconciled with each other) until you have gained proficiency.
Finally, how does one design relational tables that represent a gen-spec situation? Try looking up these two design patterns "Class-table-inheritance" and "Single-Table-Inheritance". There are two tags for these in over in Stackoverflow. There are also some pretty good presentations of the patterns on the web. I particularly like Martin Fowler's treatment. He seems to know how object modelers think. Hope this helps.