The Many to Many Evolution (1 Viewer)

Thales750

Formerly Jsanders
Local time
Today, 14:03
Joined
Dec 20, 2007
Messages
2,061
Is anyone interested in exploring what this is about?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Sep 12, 2006
Messages
15,614
@Thales750
Personally, I was waiting for you demonstrate what you mean by many to many, and illustrate how you manage it without a junction table.
 

Solo712

Registered User.
Local time
Today, 14:03
Joined
Oct 19, 2012
Messages
828
In strict data terms there is no need for a 1 to 1. The construct is used to deal with some real world issues.
I beg to differ. The one-one relationship is not called for by some abstract nature of "data" but by business or social rules. I don't care if someone wants to call it "assignment" or whatever. If a company assigns one desk to one person, one can philosophize that it really is just an instance of one person to many desks or many persons to one desks, or many persons to many desks. It takes nothing from the one-to-one relationship required by the rule. Once you take assign a desk to a person, that person can't get a second desk, or the assigned desk can't be allocated to someone else while in use. You may even believe that monogamy is a special case of polygamy but again such notion is unhelpful in organizing data. The point is that if a business/social rule calls for a pairing of data elements then one-to-one relationship is what is called for.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Sep 12, 2006
Messages
15,614
I beg to differ. The one-one relationship is not called for by some abstract nature of "data" but by business or social rules. I don't care if someone wants to call it "assignment" or whatever. If a company assigns one desk to one person, one can philosophize that it really is just an instance of one person to many desks or many persons to one desks, or many persons to many desks. It takes nothing from the one-to-one relationship required by the rule. Once you take assign a desk to a person, that person can't get a second desk, or the assigned desk can't be allocated to someone else while in use. You may even believe that monogamy is a special case of polygamy but again such notion is unhelpful in organizing data. The point is that if a business/social rule calls for a pairing of data elements then one-to-one relationship is what is called for.

Sorry, but that's not a one to one in the sense that we are discussing. That's just a normal relationship. A true 1 to 1 relationship is where both records need to use the same primary key.

Monogamy/polygamy is a good example, and deciding how to structure relationships would be part of the data analysis, but I can't see that modelling a personal relationship is ever 1 to 1. Parenthood (as opposed to marriage) is different, but it's not really a relationship, it's just a simple attribute. A parent is an ancestor of an individual. To model genealogy all you need to store for any person is the personID of the mother, and the personID of the father, and then everything is determined recursively. A grandparent is a parent of a parent. A cousin is a child of a sibling of one of your parents. A sibling is another person who shares (one of) your parents, You don't need to store a "sibling" link at all.

Marriages, divorces, and personal family circumstances are completely different. I can't believe anyone would consider those as 1-to-1. Legally polygamy is permitted in certain countries anyway, and in any event serial marriages are always possible.

If a desk is assigned to one person, you just store the personID in the desk table, as if it is an attribute of the desk, similar to the size, or the colour. You can turn it round, and store the allocated desk in the person record, and then the desk is an attribute of the employee. The ID for the desk and the employee are different. You just treat the allocation as an attribute because you aren't bothered about the history.

If you want a history of previous assignments then you need a junction table. You can't just store all the previous assignments in the desk table, or in the employee table. (well you can, but then it's not normalised). That's what I was endeavouring to understand: how the OP deals with many to many without a junction table.

Note that in your example you need indexes or similar to prevent an employee being allocated to multiple desks, or a desk being allocated to multiple employees. And then maybe your system falls down, when you decide to work 2 shifts, and you therefore DO need to allocate the same desk to multiple users., or desks are used for hot-desking where any employee can take any vacant desk. This is all just normal data analysis.

I can only repeat that in strict data terms I think you never need a one to one relationship. That is one where records in different tables have the same primary key, because they are uniquely related, so there can only ever be one (at most) related record, and you don't need any business rules to control the data.

The only situations I can conceive of needing a one to one are that
a) you have more fields that can all fit into a single record because of size restrictions, so you put some of the fields in a 1-1 table
b) you have some fields that are rarely used, so to save a bit of space, you put these in a 1-1 table
b) you want some fields subject to different security settings. (eg payroll). You don't mind all staff seeing the name, home address or payroll number of an employee, but you don't want everyone to see the salary. Name, Home Address, Payroll Number and Salary are all attributes of employee and all belong in the employee record, but you put the "confidential" stuff in a separate record/table for protection
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2013
Messages
16,553
If a company assigns one desk to one person, one can philosophize that it really is just an instance of one person to many desks or many persons to one desks, or many persons to many desks. It takes nothing from the one-to-one relationship required by the rule.
how is that a 1-1 relationship? You can have a rule that says that a desk can only be assigned to one person at any one time, That does not make it a 1-1 relationship. If it were, it means when the person leaves, then the desk would be destroyed since it cannot be associated with anyone else - similarly a person cannot change desk which is what a 1-1 relationship means.

You are describing a one to many or many to many data structure with a rule that says only one person can be assigned a desk at any one time. You may choose to call it a 1-1 relationship, but then a whale is not a fish

Edit: thought I posted this yesterday :(

 

Solo712

Registered User.
Local time
Today, 14:03
Joined
Oct 19, 2012
Messages
828
Sorry, but that's not a one to one in the sense that we are discussing. That's just a normal relationship. A true 1 to 1 relationship is where both records need to use the same primary key.

Dave,
I am not accepting the new semantics that MajP and you are introducing here. They are over the top and only confuse matters. Two tables should never "share" the same PK. That's just bad practice. Even where you partition a single table with a single identifying entity into several tables, you should always make sure that the "master table" (the one with the identifying element) is an FK to the dependent table(s). What is important in deciding whether you are joining segments of a single table (which has been partitioned for performance, or, as The Doc Man mentioned, for security), or two (or more) tables, is their independence of each other. That notion is the basis for normalizing the database. Now granted that determining whether some data element is an entity in its own right or a dependent attribute is not always easy, it should not blur the obvious: all classical table relationships(1-1,1-M, M-M) are understood to be between independent entities.

That's basically all I have to say in the matter, thanks.

Jiri
 

Solo712

Registered User.
Local time
Today, 14:03
Joined
Oct 19, 2012
Messages
828
how is that a 1-1 relationship? You can have a rule that says that a desk can only be assigned to one person at any one time, That does not make it a 1-1 relationship. If it were, it means when the person leaves, then the desk would be destroyed since it cannot be associated with anyone else - similarly a person cannot change desk which is what a 1-1 relationship means.

You are describing a one to many or many to many data structure with a rule that says only one person can be assigned a desk at any one time. You may choose to call it a 1-1 relationship, but then a whale is not a fish
All I am saying is that it is the "rule" that decides what relationship he have between data entities in any given situation. . The examples you are giving do not address the 1-1 rule. Obviously you can reassign a desk, or write a new lease for an empty apartment. But you cannot assign them to more than one client at the same time. (Your data checking should make sure of that). That's what makes it 1-1. Again, you guys are not focusing on the task at hand and instead you are creating all sorts of irrelevant scenarios out of the potentiality of other relationships.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 19, 2013
Messages
16,553
I think you are taking a term '1-1' and applying it to a certain scenario, but in databases what is meant by 1-1 is they both have the same primary key. OK in principle you can change the primary key in one table to match a different primary key in another but most developers wouldn't do that. for several reasons

I think we have to agree to disagree about what is meant by 1-1
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 13:03
Joined
Feb 28, 2001
Messages
26,999
The one-one relationship is not called for by some abstract nature of "data" but by business or social rules.

Just as an observation, data - in the abstract - HAS no business or social rules. Data - in the specific - may / will have many rules. You cannot even talk about data relationship rules without a context of some kind. Neither 1-1, 1-M, M-1, or M-M have ANY meaning without context.

Strictly speaking, the 1-1 (for security case) is VERY rare even with the Navy, but it DID happen for the table that had HIPAA data in it. That HAD to be 1-1 because you couldn't be in the Navy without having the HIPAA data in question and without the person there would have been no need for the HIPAA entry - so they really WERE 1-1 and both tables used the same PK representing that the HIPAA data was the secured portion of the more ordinary personnel record for the same person. That's the ONLY case I've ever personally seen for 1-1 that was actually required.

The "one person, one desk" rule is not 1-1 because you can have more desks than people - and because the assignment is impermanent. So somewhere in there, you will have a 1-M case where a person can be assigned to a desk (person as the 1 side of the 1-M) and a desk can be assigned to the person. But if you have more desks than people, some desks will be unassigned. The "apparent" 1-1 relation is ACTUALLY a 1-M with an M-side uniqueness constraint. But it has to be 1-M if the desk can ever be unassigned at a given time. That's because 1-1 using relational integrity doesn't allow the 1-0 case... but 1-M/constrained does. And, of course, you can reverse that argument for having a person not yet assigned a regular desk - perhaps because they are still in training class before starting the assignment? But again, the desk can't have two people but can have 0, so again it is 1-M (where person M can be 0) and a constraint on uniqueness.
 

Thales750

Formerly Jsanders
Local time
Today, 14:03
Joined
Dec 20, 2007
Messages
2,061
I always thought 1 to 1 existed in the fantasy realm where dragons live, or maybe someone wanted to split the number of fields. Like Doc's scenario.
 

Thales750

Formerly Jsanders
Local time
Today, 14:03
Joined
Dec 20, 2007
Messages
2,061
@Thales750
Personally, I was waiting for you demonstrate what you mean by many to many, and illustrate how you manage it without a junction table.
The universal table is a junction table, but for more than records, also tables.

I'm not doing contract development any more, I'm only working on my on products. I will make a simple example in the next month and post it.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Sep 12, 2006
Messages
15,614
Dave,
I am not accepting the new semantics that MajP and you are introducing here. They are over the top and only confuse matters. Two tables should never "share" the same PK. That's just bad practice. Even where you partition a single table with a single identifying entity into several tables, you should always make sure that the "master table" (the one with the identifying element) is an FK to the dependent table(s). What is important in deciding whether you are joining segments of a single table (which has been partitioned for performance, or, as The Doc Man mentioned, for security), or two (or more) tables, is their independence of each other. That notion is the basis for normalizing the database. Now granted that determining whether some data element is an entity in its own right or a dependent attribute is not always easy, it should not blur the obvious: all classical table relationships(1-1,1-M, M-M) are understood to be between independent entities.

That's basically all I have to say in the matter, thanks.

Jiri

no - but that s the point. With a true 1 to 1 relationship, they DO have the same PK.

So EmployeeID 1234 stores certain information, and
EmployeeIIDRestricted 1234 stores additional info about the same employee, and it's just not possible to have more than 1 related record between these tables.

Otherwise, you have have a 1-m relationship that you programmatically restrict to 1-1, which is a different idea.
 

Solo712

Registered User.
Local time
Today, 14:03
Joined
Oct 19, 2012
Messages
828
Look guys, you may gang up on me all you want, but you are just shooting the messenger. Much experience as you might have you don't get to decide what is what in "databases". If you want to argue that "real/true" one-to-one relationship exists only where two tables have the same PK, you better take on Microsoft (among others). I am done; have a great day!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 18:03
Joined
Sep 12, 2006
Messages
15,614
Look guys, you may gang up on me all you want, but you are just shooting the messenger. Much experience as you might have you don't get to decide what is what in "databases". If you want to argue that "real/true" one-to-one relationship exists only where two tables have the same PK, you better take on Microsoft (among others). I am done; have a great day!

Interesting links. I can see where you are coming from.
Have a good weekend. :D
 

Lightwave

Ad astra
Local time
Today, 18:03
Joined
Sep 27, 2004
Messages
1,521
I think we might be going round in circles here.

You could argue that a 1-1 relationship is the most common relationship and is the core of what a tuple is defined as. We just rarely model it specifically because it is covered by putting all the attributes in the same table and therefore naturally handled implicitly when we normalise a schema and the most likely reason for coming across it is because you are constrained in your ability to optimally normalise the data for some other reason (security in the case of the doc-man)

I sometimes look at 1 to many relationship as a covering term for the group of all relationships (1:2 1:3 1:4 1:n etc..). such that if any 1 to multiple relationship can be specified as an individual item within that list eg 1:2 it can be modelled either as two tables or in a single table. Geneology is an example here where biological mother and father can be modelled with two columns in a single persons table that references itself. The 1:2 relationship in biology is a massive one because it is so prevalent in the animal kingdom. I guess there are others but I can't think of the last time I really specifically took note of one.

Maybe the types of wheels on a tricycle?

I'm probably more interested in this Universal Table Idea.
I think I might be using it. I've not personally seen it as a theory and so I haven't nailed it down (just noticed that I m doing it)

I now use a naming convention
Every table has a primary key auto integer which I always call pkid
I reference any foreign keys as pkidt001 where the suffix denotes that it is a foreign key related to the table number. If there are more than one foreign key from the same table I will write some description as well such as pkidt001mother/pkidt001father

I guess a universal table junction table therefore might have.

Table 1
pkid
pkidt002
pkidt003
pkidt021

Is this the kind of thing you are talking about Thales?
You can relate Table 1's pkid to Table 21's pkid and in certain circumstances by defining a relationship between say Table 21 and Table 2 in this universal table at some point you may also infer a relationship between Table 2 and Table 1 without the need for having 2 junction tables one for relationship between table 1 and table 21 and one for the relationship between table 1 and table 2. I'm just thinking about this now so apologies if the logic is flawed.
 
Last edited:

Users who are viewing this thread

Top Bottom