Databases
StackOverflow2013
Postsdbo
POWhat is the best or most accepted way to store a custom value for a field that is generally relational to a table of possible values?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the best or most accepted way to store a custom value for a field that is generally relational to a table of possible values?
    primarykey
    data
    text
    <p>In my application each of my users is required to select a suburb to which to associate their profile. The <code>users</code> table has a field <code>suburb_id</code> and a table called <code>suburbs</code> has both an <code>id</code> and <code>name</code> field.</p> <p>Our <code>suburbs</code> table contains most of the suburbs that we will need, however occasionally users will need to enter suburbs that we don't have in our table, or have popped up since we populated our table.</p> <p>What is the best way in terms of database design to solve this problem.</p> <p>I had considered changing the field <code>suburb_id</code> to just <code>suburb</code> and then testing in the application whether it was an integer or a string - if it was an integer the application would assume it is related to an item in the <code>suburbs</code> table, if it was a string it would assume otherwise. However, if a user was to simply enter an integer in the <code>suburb</code> field then the application would obviously mistake it and try to match it up with a value in the table.</p> <p>Is that an acceptable way to deal with the problem (it seems gimmicky to me - I am sure there must be a better solution).</p> <p>EDIT: I would also like to avoid inserting data provided from users into the <code>suburbs</code> table (even if flagged) as I don't want to affect the quality of the suburbs data we have.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDwight
    1. USDwight
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhat is the best or most accepted way to store a custom value for a field that is generally relational to a table of possible values?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhy don't you just query the `suburbs` table? Or am I missing something.
      singulars
      1. POWhat is the best or most accepted way to store a custom value for a field that is generally relational to a table of possible values?
      1. USVinayak Garg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload