[thelist] Where to Store Username and Password in SQL Tables

rudy r937 at interlog.com
Tue Jan 28 09:40:08 CST 2003


> So I can use the email address as the foreign key, right?
> If I'm not grasping this, Rudy, then let me know.

it's difficult without pictures   ;o)

a data model diagram would help you understand the relationship

maybe i can describe it in words

you have two tables, user and manufacturer

you want a relationship where multiple users can administer a manufacturer,
but any individual user can administer only one manufacturer

that's a many-to-one relationship, with your user table on the "many" side,
and your manufacturer on the "one" side

rule:   the foreign key resides in the "many" table

some people use the term "parent-child" to describe a one-to-many
relationship, and say "the child always points to the parent"

however, others find this terminology confusing, perhaps because you have to
explain why a child doesn't have two parents and why both are in the same
table

the one-to-many relationship is also called "master-detail"

my own mnemonic, the way i remember primary and foreign keys in
relationships, is good old sql, not least because it's so nice and compact
and so takes up less room in my brain

the sql syntax that declares a foreign key is

    foreign key ( foo ) references bar

foo is the column name of the foreign key, and bar is the table name of the
table that has the primary key that the foreign key foo references

since primary keys are unique, this means that bar is the "one" table

mnemonic:  unique = one

this, in turn, by process of elimination, there being only one table left to
choose from, means that foo resides in the "many" table

easy   ;o)

so in your database, randal, user is the "many" table and manufacturer is
the "one" table

thus, user contains a column that is a foreign key that references the
primary key of the manufacturer table...

the primary key of the manufacturer table is man_ID

drum roll, please

so the user table foreign key column should be:  man_ID

alternatively user_man_ID


rudy

p.s. did this help?  if so, please let me know, maybe i should write these
long explanations up somewhere

you know, it's hard to write stuff using small words

i saw a web page yesterday that totally floored me --

  Theory of Relativity in words of four letters or less
  http://www.muppetlabs.com/~breadbox/txt/al.html





More information about the thelist mailing list