Saving records in MySQL which does not pre-exist

Multi tool use
Multi tool use












4















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?










share|improve this question

























  • 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


















4















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?










share|improve this question

























  • 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
















4












4








4








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?










share|improve this 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?







mysql sql database database-design






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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 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





















  • 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



















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














6 Answers
6






active

oldest

votes


















6





+25









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
)





share|improve this answer


























  • 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



















3














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.






share|improve this answer
























  • 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



















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.






share|improve this answer


























  • 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 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



















2














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.






share|improve this answer
























  • 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





















0














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






share|improve this answer































    0














    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.






    share|improve this answer























      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
      });


      }
      });














      draft saved

      draft discarded


















      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









      6





      +25









      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
      )





      share|improve this answer


























      • 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
















      6





      +25









      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
      )





      share|improve this answer


























      • 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














      6





      +25







      6





      +25



      6




      +25





      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
      )





      share|improve this answer















      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
      )






      share|improve this answer














      share|improve this answer



      share|improve this answer








      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



















      • 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













      3














      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.






      share|improve this answer
























      • 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
















      3














      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.






      share|improve this answer
























      • 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














      3












      3








      3







      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.






      share|improve this answer













      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.







      share|improve this answer












      share|improve this answer



      share|improve this answer










      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



















      • 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











      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.






      share|improve this answer


























      • 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 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
















      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.






      share|improve this answer


























      • 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 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














      3












      3








      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.






      share|improve this answer















      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.







      share|improve this answer














      share|improve this answer



      share|improve this answer








      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 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



















      • 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 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

















      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











      2














      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.






      share|improve this answer
























      • 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


















      2














      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.






      share|improve this answer
























      • 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
















      2












      2








      2







      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.






      share|improve this answer













      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.







      share|improve this answer












      share|improve this answer



      share|improve this answer










      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





















      • 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













      0














      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






      share|improve this answer




























        0














        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






        share|improve this answer


























          0












          0








          0







          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






          share|improve this answer













          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







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Jan 8 at 20:15









          ArthArth

          8,85942346




          8,85942346























              0














              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.






              share|improve this answer




























                0














                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.






                share|improve this answer


























                  0












                  0








                  0







                  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.






                  share|improve this answer













                  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.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 9 at 16:07









                  Deep JyotiDeep Jyoti

                  32




                  32






























                      draft saved

                      draft discarded




















































                      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.




                      draft saved


                      draft discarded














                      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





















































                      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
                      m,RFlg,79,4SSfouOcP50OD33

                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas