I have a table where I'm storing the main data I'm interested in one of the fields is a zipcode obviously this may be repeated so proper normalization suggests I should pull it out into another table so now I have my main table which has a field that points to a table of zipcodes, and this other table with all the zipcodes my question is, when I do an insert, I first insert the zipcode in it's table (if it's not already there) a key get's auto-generated for it, which I need to insert into the main table what's the proper way to do this?? what I want is to ask the database "if this zip code exists, give me it's key, if not, insert it and give me its key" how do I do this??
#1. If you are not doing any validation on the zip code or storing any dependent fields like city/state, you don't really need to move it to another table. Let it be an attribute of the table.
#2. There is a big debate about surrogate keys - Why won't you let the ZIP code be the primary key of the table zipcodes. It is a natural prinmary key ? Why generate an artificial key ?
#3. If you still want an auto-generated primary, I am assuming your table will have the columns SOME_PK, ZIP_CODE
so here is some sample code, take care to close your preparedstatements, resultsets in a finally block, and other exception handling code
<a href="http://www.auptyma.com" target="_blank" rel="nofollow">The Peak of Performance</a>
Joined: Sep 16, 2003
I'm pretty much a rookie at DB design but I was under the impression it was considered good form to always have a row identifier that did nothing but identify the row, contained no further information I recall an instance in the past of working with an existing database where failure to follow this principal caused a lot of problems, so I'm trying to avoid that (I don't really think it would be a problem in this case)
anyway I see where you're going with this query
select SOME_PK from zip where ZIP_CODE = ?
but the "?" syntax doesn't seem to work for me is it specific to a particular RDBMS? I'm using MySQL
Dave, With good design, you should have a clearly identified natural primary key. However finding a good primary key (which will never change) is a non-trivial operation, so often people will use a generated key.
The advantage of using a generated ID is that you can change any of the columns without having to cascade the update to the children.
The disadvantage is performance : If you have ZIP code as a column in the original table (rather than foreign key) then queries like zip code between 12345 and 12378 can be efficiently processed using an index.
The ? is the bind variable/query parameter. It does work in Oracle and postgreSQL. What exception are you getting ?
Joined: Sep 16, 2003
the reason I think the zip needs to be placed in it's own table is because this is a web app and it's going to be collecting several different kinds of data from users, each of which will be "identified" by it's zipcode heck, here's more info than you want to know I keep bees the app is going to collect info about when flowers bloom, when pollen starts to flow, when drones are first raised, etc each of these events is tied to the location where it happens by zip code I'm also going out to "wunderground.com" and collecting LOT'S of weather data about each zip code the idea is to collate all these events to the weather in the year they occur and then try to predict when things are going to happen this year (beekeeping is all about "timing") so, since several pieces of data are tied to the zipcode I think it deserves it's own table
the error I get is 1064 which appears to have something to do with ? being a reserved keyword I think I'm just using it improperly I'll fiddle around with it and see if I can get it to work
subject: how to get primary key after doing an insert