[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