Mysql – Child, Parent and Grand-Parent relationship where a child may not necessary have a parent or a grand parent

database-designMySQL

Here, I have some problem representing a hostel-room-inmate entities.

First an inmate is given a hostel seat,

Then he is allotted a particular hostel,

Then he is allotted a room.

My approach is that the inmate table contains a parent-link to the room table, the room table contains a parent-link to the hostel table.

table hostel(id, name)
table room(id, hostel_id, room_no)
table inmate(id, room_id, name, etc)

But while at the stage of allotment, the inmate is first allotted a hostel, in which the room_id is to be left blank, thereby meaning that the inmate is not allotted a hostel room and so on…

How do I solve this problem please…

I wanted to use it in Laravel development environment

(NB: I have some idea of creating many-to-many relationship type by creating relationship table, but just asking if there's better approach than this.)

Best Answer

I would add hostel_id to inmate, and leave Room-id empty till he arrives and the room-number is chosen.

So you could also, allow inmate to book a specific room with sea view for example

And you could check if any rooms for a date are available, for example hostel 1 has 10 room and 10 users have chosen the hostel already, you ca tell him that is not possible and give additional information when a room is free again or a hostel nearby who has vacancy.

table hostel(id, name)
table inmate(id,hostel_id, room_id, name, etc)
table room(id, hostel_id, room_no)

Related Solutions

Mysql – Implementing a one to one (1:1) relationship between Person and User

I have a person table.

Fine (just changed the name of the PK column):

person
------
person_id    PK
firstname
lastname
email
telephone
...
...

and a user table as a child. They must be 1:1 relationship, because an user cannot be repeated twice.

If the relationship is 1:1 (assuming that person is the supertpe and user is the subtype, you can handle this with having the same column both as Primary Key and Foreign Key to person:

user
----
person_id  PK  FK to person(person_id)
password
username
screenname
...
...

and so the email in the person row must not be repeated twice.

Add a UNIQUE constraint in the person.email column.

Then I have this message table which stores messages from anyone, but you can see that firstname,lastname, email,telephone are duplicated in message table. so I am thinking to refer it to person table like this below.

Fine (adjusted to previous changes). But you also need to store who sends a message and who is the receiver:

message
-------
message_id   PK
sender_id    FK  to user(person_id)         --- or to person(person_id)
receiver_id  FK  to user(person_id)         --- that depends on your requirements
subject
content
...

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL 
    FOREIGN KEY (ParentID, ChildID) 
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.

Best Answer

Related Solutions

Mysql – Implementing a one to one (1:1) relationship between Person and User

SQL Server – How to Create One-to-Many Relationship with Privileged Child

Related Question