phpmyadmin Question posted in
The official documentation can be found here.

Make sure no duplicates in "parent" of parent – Phpmyadmin

I’m using MariaDB and PHPMyAdmin, but my code is all neutral so happy to switch to Postgres or whatever, but this should be straight forward enough. I haven’t designed anything yet, just after best approaches.

I have 3 database tables that describes a premises. Let’s say a hotel.

This theoretical hotel has multiple venues – 2 restaurants and a bar. Each of those has a few different rooms/areas. In these rooms are tables that customers can sit at.

In SQL, I imagine the tables would look like this

Venues
Venue ID Venue Name
1 Restaurant 1
2 Restaurant 2
3 Bar
Rooms
Room ID Room Name Parent Venue (foreign key)
1 Patio 1
2 Function Room 1
3 Alcove 3
4 Private Dining 2
Tables
Table ID Table Name Parent Room (foreign key)
1 Table 1 1
2 Table 2 1
3 Table 3 1
4 Table 4 2
5 Table 1 3
6 Table 2 3
7 Table 3 3
8 Table 4 3
9 Table 1 4
10 Table 2 4
11 Table 3 4

I hope that data is correct :p

What I want to do is define a relationship whereas it’s impossible to add a Table Name if it already exists in that venue. It doesn’t matter what room the table is in.

E.g if I was to add another "Table 4", it would succeed in being entered if it was entered into Room 4, as Room 4 belongs to Restaurant 2, which does not already have a "Table 4". However if it was entered into any other room, it would fail as Restaurant 1 and Bar already have a "Table 4" in one of their rooms.

Now in the server side code this is fairly easy to check as I can do multiple queries or joins or a myriad of other ways, however I was wondering how to do this in SQL/PhpMyAdmin directly. I’m having a bit of trouble finding my way around MyAdmin.

Cheers

Tags:

2

Answers


  1. 0

    My recommendation is to redundantly include the parent venue in the tables table. So tables would have the additional column:

    venueID

    rooms would have a unique constraint (which is redundant):

    alter table rooms add constraint unq_rooms_venueId
    unique (venueId, roomId);

    Then tables would have a unique constraint:

    alter table tables add constraint unq_table_venueId
    unique (tableName, venueId);

    This solves the problem without having to resort to triggers.

    Login or Signup to reply.
  2. 0

    What I’d do is switch from technical IDs to composite natural keys. You can use numbers for this (i.e. give a venue a number, a room a number, maybe a table a number) or use the names, if these are guaranteed not to change. E.g.:

    venues
(
  venue_name,
  primary key (venue_name)
);

rooms
(
  venue_name,
  room_name,
  primary key (venue_name, room_name),
  foreign key (venue_name) references venues (venue_name)
)

tables
(
  venue_name,
  room_name,
  table_name,
  primary key (venue_name, room_name, table_name),
  foreign key (venue_name, room_name) references rooms (venue_name, room_name),
  unique constraint (venue_name, table_name)
)

    (If using the names for keys and your tables don’t contain any other information aside from the names, you can easily remove the tables venues and rooms of course and only keep the tables table, if you want.)

    Same thing with numbers:

    venues
(
  venue_no,
  name,
  primary key (venue_no)
);

rooms
(
  venue_no,
  room_no,
  name,
  primary key (venue_no, room_no),
  foreign key (venue_no) references venues (venue_no)
)

tables
(
  venue_no,
  room_no,
  table_no,
  name,
  primary key (venue_no, room_no, table_no),
  foreign key (venue_no, room_no) references rooms (venue_no, room_no),
  unique constraint (venue_no, name)
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search