Saving records in MySQL which does not pre-exist

Question

Let say, I have a pre-defined table called cities, with almost all the cities in my country.

When a user register himself (user table), the column cities_id in the table user stores the city id from the table cities (Foreign Key, reference table cities), something like

CREATE TABLE `cities` (
    `id` int,
    `city_name` varchar(100)
)

CREATE TABLE `user` (
    `id` int,
    `name` varchar(60)
    `****`
    `cities_id` FK
)

The user table stores the city id.

But what if I missed a few cities ... How does the user then save his city name in the user table which does not accept any city name but only IDs.

Can I have one more column city_name right after the cities_id in the table user something like

CREATE TABLE `user` (
    `id` int,
    `name` varchar(60)
    `****`
    `cities_id` FK
    `citiy name` varchar(100)
)

to record the data entered by the user at the time of registration? Can this be done?

Answer 1

You can add a type to city table tag, the user can't find their corresponding to the city allows him to type the name of his city, and then you in the city, and will create a corresponding record in the table type marked as a special status (convenient operating personnel check and correction), at the same time to save the record id to the user record

CREATE TABLE `cities` (
    `id` int,
    `city_name` varchar(100),
    `type` int,
)

CREATE TABLE `user` (
    `id` int,
    `name` varchar(60)
    `****`
    `cities_id` FK
)

Answer 2

As @Joakim mentioned in the comment, from a DB perspective, as cities_id is a foreign key referencing to the cities table, inserting a record to the user table will fail if the city in question is not already there in the table.

From a programming perspective, if you want a city which is not there in the table should be first inserted automatically whenever a user is registering, it is possible. Assuming you are using Java and Hibernate and User entity contains City entity, then calling saveOrUpdate() method on the user entity will cause the city record to be inserted if not already there, and a user record will then be inserted into the User table.

Answer 3

That's how I would quickly solve this

Create an additional table to store the missing cities, that will be introduced by users

CREATE TABLE `cities_users` (
`id` int,
`city_name` varchar(100),
`added_by` varchar(100),
`added_TS` DATETIME DEFAULT CURRENT_TIMESTAMP
);

Create a VIEW that UNION the 2 cities tables :

CREATE VIEW all_cities AS
SELECT id, city_name FROM `cities`
UNION ALL
SELECT id, city_name FROM `cities_users`;

Whenever a user register, you query the VIEW to check if the user's city exists. That way you'll kknow if a city exists in your original table OR the cities introduced by users.

If not, you INSERT the new city in the cities_users table (along with the user that created it for logging purposes).

You should generate a unique ID properly, ie one that can't ever exists in the cities table. You can do this in various ways, here's a quick example : Take the last ID in the cities_users table and add 1 million to it. Your cities_users IDs will be like: 1000001, 1000002, 1000003

And finally, you insert the generated cities_users ID in the users table.

Having a separate table for user inputs should help you to keep the database clean :

1. Your original cities table remains totally unchanged
2. You will know easily at all times the new cities added by whom and when, and you can create a small interface to review and manage that.
3. Your users are working for you to complete your database.

Answer 4

If a user suggest a new city you should create a new record into cities table and store city_id into users table. This is the best way to store the table records.

Answer 5

I feel like it should be pointed out, despite answers to the contrary, that your original suggestion of adding a city_name column to the table will work fairly well

If you allow both cities_id and city_name to be nullable then you can validate that one and only one of them is set in the application logic

The benefit of this approach is that it would keep your city table 'pure' and allow you to count duplicates of and analyse the user supplied cities easily

It would however add a very sparse nullable city_name column in your table

I guess it depends on how you want to get the city from the user, (drop-down + text box for others, text-box with suggestions, just a text box) and what you plan to do with the cities you have gathered

You could even change the label to 'city (or nearest city)' with a hard-coded drop-down, or searchable drop-down, and not allow user supplied cities

Answer 6

If you have a buffer table where the raw data is put in, i.e. the relationship between city_name, user_name

CREATE TABLE `buffer_city_user` (
`buffer_id` int,
`city_name` varchar(100),
`user_name` varchar(100),
);

you can first process the buffer table for new city_names - if found, insert into table cities.

Then insert the user info - any new city-names should already be in the cities table and no foreign key issues will occur.