Thread: Sufficient Primary Key?
Hello all.
I'm working on a library catalog and trying to decide what to use
for the primary key for the authors. Do you think that the first
three letters of first and last name with the birth year would be
sufficient (e.g., Alan Watts would be ALAWAT1915). So, essentially,
do you think there there would ever be two authors with the same
first and last name AND the same birth year? I really don't want to
inject the middle name into there because I can't find the middle
name (or even middle initial) of most authors!
Thanks.
--Dan
-----------------------------------------------------------------------
Daniel G. Delaney The Louisville Times Chorus
Dionysos@Dionysia.org www.LouisvilleTimes.org
www.Dionysia.org/~dionysos/ Dionysia Design
ICQ Number: 8171285 www.Dionysia.com/design/
-----------------------------------------------------------------------
I doubt, therefore I might be.
On Fri, 24 Jul 1998, Dan Delaney wrote:
> I'm working on a library catalog and trying to decide what to use
> for the primary key for the authors. Do you think that the first
> three letters of first and last name with the birth year would be
> sufficient (e.g., Alan Watts would be ALAWAT1915). So, essentially,
> do you think there there would ever be two authors with the same
> first and last name AND the same birth year? I really don't want to
> inject the middle name into there because I can't find the middle
> name (or even middle initial) of most authors!
You can do multi-column keys in PostgreSQL, I believe, which is
essentially what you would be doing by combining that data into a single
column.
BTW, Alan Watts is a favourite of mine.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"The Number of UNIX installations has grown to 10, with more expected."
-- The UNIX Programmer's Manual, 2nd Edition, June, 1972
On Fri, 24 Jul 1998, Brett W. McCoy wrote:
> You can do multi-column keys in PostgreSQL, I believe, which is
> essentially what you would be doing by combining that data into a single
> column.
I don't like doing that because I don't want to store the entire
name in every other table which needs to be linked with this one.
So, for instance, in the table that keeps track of what authors go
to what book, I want it to just have two fields, the Book's primary
key and the Author's primary key, and I'd prefer those primary keys
to be nice and small instead of, say, the entire title and copyright
year for the book and the entire first and last name and date of
birth for the author. See what I mean?
> BTW, Alan Watts is a favourite of mine.
Mine too.
--Dan
-----------------------------------------------------------------------
Daniel G. Delaney The Louisville Times Chorus
Dionysos@Dionysia.org www.LouisvilleTimes.org
www.Dionysia.org/~dionysos/ Dionysia Design
ICQ Number: 8171285 www.Dionysia.com/design/
-----------------------------------------------------------------------
I doubt, therefore I might be.
On Fri, 24 Jul 1998, Dan Delaney wrote:
> I don't like doing that because I don't want to store the entire
> name in every other table which needs to be linked with this one.
> So, for instance, in the table that keeps track of what authors go
> to what book, I want it to just have two fields, the Book's primary
> key and the Author's primary key, and I'd prefer those primary keys
> to be nice and small instead of, say, the entire title and copyright
> year for the book and the entire first and last name and date of
> birth for the author. See what I mean?
I see your point, and didn't realize that you had a bunch of other tables
linked. I have actually done something similar to what you want to do, but
with drug numbers and product codes, mainly because using the drug numbers
alone would involve duplicates, and matching approval records to patent
records. There's only so much one can normalize.
Brett W. McCoy
http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
"The Number of UNIX installations has grown to 10, with more expected."
-- The UNIX Programmer's Manual, 2nd Edition, June, 1972
> > > I don't like doing that because I don't want to store the entire > name in every other table which needs to be linked with this one. > So, for instance, in the table that keeps track of what authors go > to what book, I want it to just have two fields, the Book's primary > key and the Author's primary key, and I'd prefer those primary keys > to be nice and small instead of, say, the entire title and copyright > year for the book and the entire first and last name and date of > birth for the author. See what I mean? > Here is a suggestion. Create a table that has an int4 as the key. Cause this value to be auto incremented based off of a sequence each time an insert is done to it. The other fields would include the actuall lookup infromation (i.e. their first and last name, and the code you wanted to append to that). Now in all your tables use the int4 key to reference the lookup. Once you do this, on all your forms that look up information based off of this first and last name and the other value, you can have them type just that, and then you can do a query that looks in the table to find the int4 key, and then use that value to find the actual record for which you are looking. This schema will totally get rid of the need to concern yourself with making the lookup information "unique", because if it is not unique your query would simply return multiple records (hopefully only two or three) from which the user can choose. I hope this helps...james > > BTW, Alan Watts is a favourite of mine. > > Mine too. > > --Dan > > ----------------------------------------------------------------------- > Daniel G. Delaney The Louisville Times Chorus > Dionysos@Dionysia.org www.LouisvilleTimes.org > www.Dionysia.org/~dionysos/ Dionysia Design > ICQ Number: 8171285 www.Dionysia.com/design/ > ----------------------------------------------------------------------- > I doubt, therefore I might be.