Database design question - field or new table + one for many

Question

I create a database for the event management site - there is a table of places and an event table. Each event is held in the venue of the event (stores the identifier of the venue), and each place in the city. It should be possible to search by city if the city should be a field in the table of places (which can lead to duplication of cities due to spelling errors) or there should be a table of cities (each with an identifier and a name) and a one-to-many table linking the cities with places (cityid, siteid)?

I know this is a pretty simple question, but I'm not sure if this will be a worthy addition or two extra tables.

Thank you in advance

[EDIT] @tvanfosson: Changed from many-to-many to one-to-many, as each place is associated with one city.

Answer 1

Use a separate table - this way you have a main list of cities to fill in drop-down lists and / or autostart fields, and you save space by saving redundant identifiers instead of strings. If you have a million places and only a thousand cities, this is a significant savings, both in terms of storage and in terms of query speed, since you do not have to read as many disks as it kills performance.

You should probably indicate more than just a city, but also a state (so you know where Springfield is located in).

Answer 2

I assume that many cities are fixed, relatively small and unlikely to be updated (you can always add new ones for spelling). In this case, you can select a city from the drop-down list that is loaded from the XML file and save the selected value in the database in a column. I would avoid using user input because of the chances of incorrect input.

If you have a more hierarchical structure in which cities are in counties that are in states, then a tabular approach might be more appropriate since you can have cities with the same name in several places. In this case, I think that cascading drop-down lists are easier to manage with database queries than with XML.

Note. There is probably no “right” answer, as it depends a lot on your circumstances.

Answer 3

It might be worth considering the address table with the city column.

The decision really depends on what the other functions are, and whether the database will ever be used for other functions in the future.

This is also a subjective choice, in my opinion, a separate table of the city is probably too normalized.

Answer 4

Translate many-to-many relationships connecting cities and venues with a one-to-many relationship with an additional table (for example, event_city), where you can store all the places for each city, and then bind the table events and the location_city table while keeping event_identifier in a table event.

Answer 5

You should probably have a table of cities - even if you don't need it now, you might want to add zip codes or have cities in the states.

Answer 6

If I understand your situation correctly, the city table is required, including the state. You can add new cities / states as needed (check availability in the table before adding). This will be much more efficient and avoid duplication, but the names are wrong.

Answer 7

Do not store city names in venues. Instead, highlight the city identifier and save it in place. Before you join the event search for a specific city, resolve the city identifier with the city name and use it as criteria for your participation. This can be implemented in a stored procedure for convenience and increased performance.