alcove | Erlang system containers

I have data which looks like:

...

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

...

I have a dataframe like the following:

...