2

In my application each of my users is required to select a suburb to which to associate their profile. The users table has a field suburb_id and a table called suburbs has both an id and name field.

Our suburbs 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.

What is the best way in terms of database design to solve this problem.

I had considered changing the field suburb_id to just suburb 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 suburbs table, if it was a string it would assume otherwise. However, if a user was to simply enter an integer in the suburb field then the application would obviously mistake it and try to match it up with a value in the table.

Is that an acceptable way to deal with the problem (it seems gimmicky to me - I am sure there must be a better solution).

EDIT: I would also like to avoid inserting data provided from users into the suburbs table (even if flagged) as I don't want to affect the quality of the suburbs data we have.

Dwight
  • 12,120
  • 6
  • 51
  • 64

2 Answers2

2

There might be several ways to handle that, but I think the most clean way is to leave the suburbs and userstable as they are, and add the suburb to the suburbs table in case the suburb doesn't already exist. Maybe with a flag that this in an user generated entry for later cleanup.

I had considered changing the field suburb_id to just suburb 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 suburbs table, if it was a string it would assume otherwise.

That can lead easily to performance issues.

fab
  • 1,839
  • 15
  • 21
  • I considered the first option also, however I would like to avoid adding new data to the `suburbs` table at all as it may be difficult to validate etc. I want to ensure that the data we have in that table is data we know is 100% valid. – Dwight Jan 02 '12 at 08:56
0

There's no magic bullet for this kind of problem. If there's a foreign key reference, you only have a few choices.

  • Let the user insert rows into the suburbs table.
  • Don't let the user insert rows into the suburbs table.
  • Remove the foreign key reference.
  • Replace the suburbs table with supertype/subtype tables, where the supertype would contain all suburbs, and the subtype tables would distinguish user-submitted suburbs from validated suburbs.
Community
  • 1
  • 1
Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185