Saving records in MySQL which does not pre-exist

Multi tool use
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?
mysql sql database database-design
add a comment |
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?
mysql sql database database-design
The user can’t unless the user also can create a new city in thecities
table
– Joakim Danielson
Jan 1 at 12:12
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
1
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
1
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30
add a comment |
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?
mysql sql database database-design
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?
mysql sql database database-design
mysql sql database database-design
edited Jan 4 at 23:36


GMB
14.3k2824
14.3k2824
asked Jan 1 at 12:04


Daksh BDaksh B
661531
661531
The user can’t unless the user also can create a new city in thecities
table
– Joakim Danielson
Jan 1 at 12:12
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
1
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
1
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30
add a comment |
The user can’t unless the user also can create a new city in thecities
table
– Joakim Danielson
Jan 1 at 12:12
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
1
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
1
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30
The user can’t unless the user also can create a new city in the
cities
table– Joakim Danielson
Jan 1 at 12:12
The user can’t unless the user also can create a new city in the
cities
table– Joakim Danielson
Jan 1 at 12:12
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
1
1
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
1
1
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30
add a comment |
6 Answers
6
active
oldest
votes
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
)
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
add a comment |
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.
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
add a comment |
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 :
- Your original cities table remains totally unchanged
- 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.
- Your users are working for you to complete your database.
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
For even simpler usage, insertion of missing cities intocities_user
could be automated using aTRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.
– GMB
Jan 4 at 23:34
add a comment |
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.
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
add a comment |
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
add a comment |
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.
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995274%2fsaving-records-in-mysql-which-does-not-pre-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
6 Answers
6
active
oldest
votes
6 Answers
6
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
)
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
add a comment |
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
)
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
add a comment |
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
)
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
)
edited Jan 5 at 12:59


Angel M.
1,295414
1,295414
answered Jan 5 at 12:14
stormbaronstormbaron
1011
1011
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
add a comment |
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
I like this idea, I'm geeking out over an imagined CMS that allows you to approve, edit, combine and reject user suggested cities :)
– Arth
Jan 8 at 19:51
add a comment |
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.
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
add a comment |
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.
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
add a comment |
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.
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.
answered Jan 1 at 12:33
SriramSriram
513
513
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
add a comment |
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
can you show me an article/example/link.
– Daksh B
Jan 2 at 5:27
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
journaldev.com/3481/…
– Sriram
Jan 2 at 5:39
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
that is java and hibernate ... anything in line with php and mysql?
– Daksh B
Jan 2 at 5:52
add a comment |
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 :
- Your original cities table remains totally unchanged
- 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.
- Your users are working for you to complete your database.
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
For even simpler usage, insertion of missing cities intocities_user
could be automated using aTRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.
– GMB
Jan 4 at 23:34
add a comment |
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 :
- Your original cities table remains totally unchanged
- 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.
- Your users are working for you to complete your database.
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
For even simpler usage, insertion of missing cities intocities_user
could be automated using aTRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.
– GMB
Jan 4 at 23:34
add a comment |
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 :
- Your original cities table remains totally unchanged
- 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.
- Your users are working for you to complete your database.
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 :
- Your original cities table remains totally unchanged
- 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.
- Your users are working for you to complete your database.
edited Jan 24 at 21:37
answered Jan 3 at 16:47


Thomas GThomas G
7,28071931
7,28071931
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
For even simpler usage, insertion of missing cities intocities_user
could be automated using aTRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.
– GMB
Jan 4 at 23:34
add a comment |
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
For even simpler usage, insertion of missing cities intocities_user
could be automated using aTRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.
– GMB
Jan 4 at 23:34
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
thank you buddy, your suggestion makes a lot of sense. I would like to try your solution before I accept your answer. Pls give a me a day or two. I'll revert quick. Thank you again.
– Daksh B
Jan 3 at 16:59
1
1
For even simpler usage, insertion of missing cities into
cities_user
could be automated using a TRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.– GMB
Jan 4 at 23:34
For even simpler usage, insertion of missing cities into
cities_user
could be automated using a TRIGGER
when a new user registers with an unknown city. That would save you querying the view in the first place.– GMB
Jan 4 at 23:34
add a comment |
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.
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
add a comment |
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.
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
add a comment |
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.
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.
answered Jan 3 at 17:07


Asif HussainAsif Hussain
344
344
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
add a comment |
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
I do not want to lose a user just because his city wasn't already in my records and this is what I am trying to solve. If the user's city isn't in my record I still want him to add his city. Many people will tend to forget that they made a request and wont come back. I find Thomas G. answer much inline to what I am looking for. Thank you for your suggestion though.
– Daksh B
Jan 3 at 17:18
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
i was not asking to loos a user as the user is the very valuable asset. I was asking to add city first then use it's id into user users table along with user details. if you add in two tables you will face a problem while performing a search from the cities table.
– Asif Hussain
Jan 3 at 17:21
add a comment |
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
add a comment |
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
add a comment |
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
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
answered Jan 8 at 20:15
ArthArth
8,85942346
8,85942346
add a comment |
add a comment |
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.
add a comment |
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.
add a comment |
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.
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.
answered Jan 9 at 16:07
Deep JyotiDeep Jyoti
32
32
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53995274%2fsaving-records-in-mysql-which-does-not-pre-exist%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
9IWdVG3 ZpQ5aIM8
The user can’t unless the user also can create a new city in the
cities
table– Joakim Danielson
Jan 1 at 12:12
@Jaokim Danielson, how does facebook do this? When you fill the city column it suggest you cities name and if it does not exist you get to write your own. I believe FB stores the city ID cz when you click the city hyperlink in a profile it shows interesting places related to that cities
– Daksh B
Jan 1 at 12:26
1
Well your question is very broad and it's unclear how you want to solve this, in the client code, in server-side code or completely in sql? But the basic logic is very simple, if the city doesn't exist then store it and used the new id for that city when storing the user.
– Joakim Danielson
Jan 1 at 13:02
1
Do you want to allow users to enter arbitrary data in cities table (and make all sort of typos, duplicates or made-up/unofficial names)? I would rather suggest that you download the data from some official/well maintained source so that the probability of someone not finding their city name is minimal.
– Salman A
Jan 1 at 13:30