按国家搜索场地似乎有问题

Question

Overview: I am making a concept site for events and because events can tour around a country and the world, I wanted to make it easy for the people who are adding the events, because we already have a list of venues, and new venues are being added each day we have set up our database ER with the following tables

country state suburb

The state table links back to the country table using the id of the country, while the suburb table links back to the state.

Now while this is all good and fine, I think I have found an issue, it more about how do I get the php code to know that that country to search for all the venues that are in that country, if I have not store the country or state id in the venue. I have only store the suburb.

I wanted to have less columns in my database, but I am not thinking why not put those two in, this was we will be able to find the venues, which in return will find the events linked to that venue.

I do wonder if their is a better way to do this, and any feedback would be helpful.

To clarify venues table only links to suburb, and suburb links to state and state links to country.

Here is a copy of part of my ER diagram

Answer 1

4 Tables: Country, State, Suburb, Venue

• Venue has an FK to Suburb
• Suburb has an FK to State
• State has an FK to Country

Solution 1: The really bad SQL (don't do this, but kinda fun to think out)

SELECT * FROM venues WHERE suburb_id IN (
    SELECT suburb_id FROM suburb WHERE state_id IN (
        SELECT state_id FROM state WHERE country_id IN (
            SELECT country_id FROM country WHERE name = ?
        )
    )
)

Solution 2: Create a lookup table like this for venue to location storing all the relevant values, and then you can look everything up via that lookup table

VENUE_LOCATION
id (INTEGER), PK, AUTO INCREMENT
venue_id (INTEGER), FK to venue id
suburb_id (INTEGER), FK to suburb id
state_id (INTEGER), FK to state id
country_id (INTEGER), FK to country id

Only thing to watch out for is that someone might give you invalid values during insertion or modification of the record, so what you can do is make this table READ, INSERT and DELETE only, and to enforce the integrity from the application layer.

So, make your code only take in the inputs of VENUE and SUBURB, and your back end code can determine the state and country ids from internal lookups VS user input.

Then your query to retrieve all the venues by country would be:

SELECT * FROM venues WHERE venue_id IN (
    SELECT venue_id
        FROM venue_location INNER JOIN country
        ON country.id = venue_location.country_id
        WHERE country.name = ?
)