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

Question

I'm designing a database for an events management site - there is a table of venues and a table of events. Each event is in a venue (stores the venue's id) and each venue is in a city. It must be possible to search for event by city, should city be a field in the venues table (which could possibly result in duplicate cities due to misspellings) or should there be a table of cities (each with an id and a name) and a one-to-many table linking cities with venues (cityid, venueid)?

I know this is a pretty basic question, but I'm not really sure whether the extra join and extra two tables would be worth it or not.

Thanks in advance

[EDIT] @tvanfosson: Changed from many-to-many to one-to-many since each venue is associated with a single city.

Answer 1

It might be worth considering having an address table, with a city column.

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

It's also a subjective choice, in my opinion having a seperate city table is probably slightly too normalised.

Answer 2

I assume that the set of cities is fixed, relatively small, and not likely to be updated (for spelling, you could always add new ones). In this case provide the ability to choose cities from a dropdown that is fed from an XML file and store the selected value in the database in a column. I would avoid using user-supplied input due to the chances for incorrect input.

If you have a more hierarchical structure where cities are in counties, which are in states, then a table-based approach may be more appropriate since you could have cities with the same name in multiple locations. In this case I think cascading dropdowns are easier to manage using database queries than via XML.

Note: there is probably no "right" answer as it is highly dependent on your circumstances.

Answer 3

Translate many-to-many relations linking cities and venues to one-to-many relation with the additional table (something like venue_city) where you can store all the venues for each city and then link the tables event and the table venue_city storing the venue_city id into the table event.

Answer 4

Isn't it One to Many? There may be more venues in one city, but only one city per venue.

Answer 5

Use a separate table -- that way you have a master list of cities to populate dropdowns and/or autosuggest fields, and you save space by storing ids instead of strings redundantly. If you have a million venues, and only a thousand cities, that's significant savings, both in terms of storage, and in terms of query speed -- since you won't have to read as much off disk, which is what kills performance.

You should probably specify more than just a city, but also the state (so that you know which Springfield the venue is located in).

Answer 6

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 be in states.

Answer 7

If I understand your situation correctly, a city table is a must, including the state. You can add new city/states as needed (check for existence in the table before adding). It will be much more efficient and avoid duplicate, but misspelled city names.

Answer 8

Don't store the city names in the venues. Instead allocate a city-id and store that in the venue. Before you do your join to find events for a particular city resolve the city-id from the city name and use that as your join criteria. This can be implemented in a stored procedure for convenience and increased performance.

Answer 9

how to join two fields that in separate table into one field..??? help me =|

e.g TABLE1:Project

field1 : Project_ID

TABLE2:Client

field1 : Client_ID

TABLE3:clientPro

   field1 : Sequence_Num(autoNum), Client_ID,Project_ID

so that;

Project_ID = Client_ID + Sequence_Num