0

I understand where to put foreign keys for 1:1.

Owner(pet_id) [HAS] Pet
Pet(owner_id) [BELONGS TO] Owner

But consider an example like:

Profile(section_id) [HAS] MoviesSection
Profile(section_id) [HAS] BooksSection

MoviesSection(profile_id) [BELONGS TO] Profile
BooksSection(profile_id) [BELONGS TO] Profile

How do I decide whether I want to use the HAS or the BELONGS TO e.g. if I wanted to "grab all the sections of a profile to display at once". It feels like either would work?

HAS

profile
---
id movies_section_id books_section_id

movies_section
---
id favorite_movie favorite_actor

books_section
---
id favorite_book favorite_author

vs.

BELONGS TO

profile
---
id

movies_section
---
id profile_id favorite_movie favorite_actor

books_section
---
id profile_id favorite_book favorite_author

What additional questions should I be asking to decide? For example if a MoviesSection cannot exist without a Profile, would that make a difference?

atkayla
  • 8,143
  • 17
  • 72
  • 132
  • Is this a particular rdbms? – Juan Carlos Oropeza Jun 09 '18 at 23:34
  • I normally use `belongs to` when i am speaking of the child and the parent can have many children. For example, *A team has one or more players. A player belongs to one team.* You can also say: *A team has one or more players. A player has one team.* I guess it is just preference. – CodingYoshi Jun 10 '18 at 00:16
  • @CodingYoshi Thanks, I think the parent child idea helps a bit. So if you were doing "get a team's coach" (only one coach), you'd do Coach(team_id[fk])? – atkayla Jun 10 '18 at 03:30
  • @kayla Another way to think about it is a *whole* and *parts* of a *whole*. For example, one or many players (parts) make a team (whole). A team (whole), however, has one or many players (parts). Another way is to draw a huge circle and call it team. Within the circle will be one or more smaller circles for each player. Thus it makes sense to say one team has many players. The players belong (inside the circle) to a team. The team will also have one or more circles for coaches (coach, assistant coach etc.) – CodingYoshi Jun 10 '18 at 04:32
  • @CodingYoshi The one-to-many relationship is clear to me. Team<-[1:m]-Player(team_id), however the confusion is where it's a 1:1. For example, Person-[1:1]-SSN. Here you could do Person(ssn_id)-[HAS]->SSN or Person<-[BELONGS_TO]-SSN(person_id). I guess with your theory a person is the "bigger whole", so I would use Person<-[BELONGS_TO]-SSN(person_id), but this makes me wonder when I would ever use HAS. For example here https://stackoverflow.com/a/7296873/1736601 they use Student(address_id)-[HAS]->address. Is that just a bad example? – atkayla Jun 10 '18 at 04:44
  • @kayla No that makes no sense. How can a person belong to an SSN? Each person has one SSN. Am i wrong? You cannot have many persons having one SSN. You are thinking about this like a formula but it is not. You have to think about each individual case: in one case it may make sense but in another it makes no sense. But in the case of SSN it never makes sense to have 1 to many. – CodingYoshi Jun 10 '18 at 05:23
  • @CodingYoshi I think you misread (see direction of arrow). I am saying `Person-[1:1]-SSN` seems like it could be either `Person(ssn_id)-[HAS]->SSN` or `SSN(person_id)-[BELONGS_TO]->(Person)`, no? – atkayla Jun 10 '18 at 05:42
  • @kayla Ok but based on my past comments I would not say person *belongs* to ssn because it is not one to many. It is 1:1 so I would use *has*. – CodingYoshi Jun 10 '18 at 06:01

1 Answers1

1

Maybe not the answer you want but I wanted to put it in SQL terms. The section you asked about:

Profile(section_id) [HAS] MoviesSection
Profile(section_id) [HAS] BooksSection

MoviesSection(profile_id) [BELONGS TO] Profile
BooksSection(profile_id) [BELONGS TO] Profile

would look in SQL like:

create table profile (
  profile_id
);

create table moviessection (
  section_id,
  profile_id,
  constraint fk1 foreign key (section_id) references profile (profile_id)
);

create table bookssection (
  section_id,
  profile_id,
  constraint fk2 foreign key (section_id) references profile (profile_id)
);
The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • So you choose BELONGS TO over HAS. Why not: `create table profile (profile_id, movies_section_id, books_section_id)` if I know one profile should have one movies section and one books section? – atkayla Jun 10 '18 at 03:02
  • The option you present is a solution, but not a great one. It would require to store multiple `movies_section_id` and `books_section_id` on each profile row. That doesn't even conform with the first normal form. It's theoretically possible but would have horrible performance, locking, and transactionality qualities. – The Impaler Jun 10 '18 at 03:16
  • Sorry, I don't seem to understand. I know 1NF is do not store multiple values in a column. In this case, a profile is tied to one movies section, as well as one books section. How does this violate? EDIT: Oh, there is another part to 1NF! "Do not store multiple columns that are related" in this case "sections". Thank you! – atkayla Jun 10 '18 at 03:21