Factoring zeros in material style relationships
Given the scheme
PERSON {name, spouse}
where PERSON.spouse is a foreign key to PERSON.name, NULL will be required if the person is unmarried or we have no information.
Going with an argument against zeros, how do you avoid them in this case?
I have an alternative circuit
PERSON {name}
SPOUSE {name1, name2}
where SPOUSE.name * are FK for HUMAN. The problem I see here is that there is no way to ensure that someone only has one spouse (even with all the UNIQUE restrictions possible, one could have two spouses).
What is the best way to reject zeros in terms of material styling?
source to share
I think that the use of NULL and the absence of duplicates for this type of relationship makes the definition of the schema more difficult than it really should be. Even if you allow nulls, it is still possible for a person to have more than one spouse or to have conflicting records, for example:
PERSON { A, B }
PERSON { B, C }
PERSON { C, NULL }
You will need to enter more details such as gender (or "number of spouses" for same-sex marriages?) To ensure that, for example, only persons of the same type are allowed to have a spouse. The referee of the other person will be determined by the recording of the first person. For example:.
PERSON { A, FEMALE, B }
PERSON { B, MALE, NULL }
PERSON { C, FEMALE, NULL }
... So only HUMANITIES who are WOMEN can have a non-zero SPOUSE.
But IMHO, this is super complex and unintuitive even with NULL. Without NULL, it's even worse. I wouldn't do this kind of scheme if you literally had no choice.
source to share
Well, I would use auto-incrementing ids first, since of course someone could have had the same name. But I believe you intend to do so and will not attack him. However, how does the argument against NULL go? I have no problem with NULL and I think this is a suitable solution for this problem.
source to share
I'm not sure why no one has pointed this out yet, but it's actually pretty easy to make sure that a person only has one spouse using pretty much the same model as in your question.
For now, I will ignore the use of the name as the primary key (it can change and duplicates are quite common, so this is a bad choice), and I will also discard the possible need for historical tracking (you might want to add some kind of effective date so that you knew WHEN they were spouse). Joe Celko wrote some good materials on temporal modeling, but I don't remember which book he was at at the moment) Otherwise, if I get divorced and get married, you will lose that I had another spouse at another time - maybe it doesn't matter to you though.
Alternatively, you can split the name into first_name, middle_name, last_name, prefix, suffix, etc.
Given these reservations ...
CREATE TABLE People
(
person_name VARCHAR(100),
CONSTRAINT PK_People PRIMARY KEY (person_name)
)
GO
CREATE TABLE Spouses
(
person_name VARCHAR(100),
spouse_name VARCHAR(100),
CONSTRAINT PK_Spouses PRIMARY KEY (person_name),
CONSTRAINT FK_Spouses_People FOREIGN KEY (person_name) REFERENCES People (person_name)
)
GO
If you want spouses to appear in the People table, you can also add FK. At this point, however, you are dealing with a bidirectional link, which gets a little more complicated.
source to share
You can use a trigger to enforce a constraint. PostgreSQL has constraint triggers , a particularly good way to defer constraint evaluation until the appropriate time in a transaction.
From Fabian Pascal's Practical Questions in Database Management, pp. 66-67:
Stored procedures - regardless of whether they are preferred for application level, but they are practically inferior and riskier than declarative support, since they are more burdensome to write, error prone and cannot Optimize the DBMS.
...
Choose a DBMS with the best declarative integrity. Given the significant gaps in such product support, knowledgeable users are at least able to mimic correctly, although with procedural and / or application code constraints, the DBMS is not supported.
source to share
To define the relationship, you need a TABLE person and a separate Partner_Off table.
Person (id, name, etc.);
Partner_Off (id, partner_id, relationship);
To deal with a more complex social situation, you probably need some dates, plus to simplify sqls you need one entry for (fred, wilma, husband) and a corresponding entry for (wilma, fred, wife).
source to share