Geek asks and answers

Recommended value in relation to one to many, in what table should it be?

They say that I have one, many relationships, where there are two tables, the Person table and the Belonging table. Now each person has ONLY ONE favorite affiliation, and a certain affiliation cannot belong to another person.

My question is: where is it better to save this information? In Person table as favorite_belonging_id or in Belonging table as is_favorite record? In my opinion, the first choice seems to be the best version, but I would like to hear what noble people say about it.

EDIT: A person has many things, but only one favorite affiliation and each affiliation can belong to only one person. This is one of many associations.

+5
source share
7 answers

I will be tempted to go with your first sentence (a column favourite_belonging_idin the table Person), since then you can create a foreign key link from (person_id, favourite_belonging_id)to (owner_id, belonging_id)to the Belongingtable.

If someone went on a different flag creation route is_favouritein the table Belonging, then there is no obvious way to ensure the 1: 1 nature of the relationship associated with the persona favorite (a composite index will UNIQUEno (owner_id, is_favourite)longer succeed if a person has several things that are not their favorite) .

, , Person, , Belonging. , Favourites UNIQUE ( PRIMARY) person_id.

+3

, .

, , , person_id , , .

, , - .

: - , - , .

+2

, , , , , , , .

, , : , .

user_id , . user_id , .

, "" , , . , , , . JOIN, , , , ; , , , (, , ), , JOIN , 1:1 , .
, : (/switch) , , , , . , , , , . , , ; , , ( , ) , .

, , , .

+1

, . , , , , .

, , people ( persons) belongings. people :

person_id INT AUTO_INCREMENT,
other columns as necessary,

PRIMARY KEY (person_id)

belongings :

belonging_id INT AUTO_INCREMENT,
person_id    INT NOT NULL,
is_favourite enum ('1'),
other columns as necessary,

PRIMARY KEY (belonging_id),
FOREIGN KEY (person_id) REFERENCING people (person_id),
UNIQUE (person_id, is_favourite)

is_favourite nullable enum . , (person_id, is_favourite), person_id (null) is_favourite, , , , . person_id is_favourite = '1', .

+1

. , person_favourite_belonging, :

CREATE TABLE person
( person_id INTEGER NOT NULL
--- various other columns about Persons
, PRIMARY KEY (person_id)
) ;

CREATE TABLE belonging
( belonging_id INTEGER NOT NULL 
, person_id INTEGER NOT NULL 
--- various other columns about Belongings
, PRIMARY KEY (belonging_id)
, UNIQUE KEY (person_id, belonging_id)       --- this Unique constraint is needed
, FOREIGN KEY (person_id)
    REFERENCES person (person_id)
) ;

CREATE TABLE person_favourite_belonging
( person_id INTEGER NOT NULL 
, belonging_id INTEGER NOT NULL
, PRIMARY KEY (person_id)
, FOREIGN KEY (person_id, belonging_id)      --- for this Foreign Key constraint
    REFERENCES belonging (person_id, belonging_id)
) ;

. , . :

  • ( ):
  • , , .
  • NOT NULL.
  • .
  • , 2 ( ) , "".

( ): SQL, ?

+1

favourite_thing - FK ( , ), .

UPDATE:

DROP table belonging;
CREATE table belonging
        ( id INTEGER PRIMARY KEY
        , description varchar
        );

DROP table person;
CREATE table person
        ( id INTEGER PRIMARY KEY
        , description varchar
        , favourite_thing INTEGER REFERENCES belonging (id)
        );

-- Now add the unique constraint
-- NOTE: favourite_thing can still be NULL
ALTER TABLE person
        ADD CONSTRAINT must_be_unique UNIQUE (favourite_thing)
        ;

UPDATE 2: if each item belongs to exactly one person, you can add an owner field to things:

CREATE table belonging
        ( id INTEGER PRIMARY KEY
        , owner_id INTEGER NOT NULL REFERENCES person(id)
        , description varchar
        );

DROP table person CASCADE;
CREATE table person
        ( id INTEGER PRIMARY KEY
        , description varchar
        , favourite_thing INTEGER REFERENCES belonging (id)
        );

ALTER TABLE person
        ADD CONSTRAINT must_be_unique UNIQUE (favourite_thing)
        ;
0
source

In fact, you represent a one-to-one relationship. So you can: 1. Hold it in the Person table. 2. Keep it in the table accessories. 3. Hold it in both. 4. Keep it in a separate table.

-1
source

More articles:


All Articles