I have one question that may seem too basic for this list, but I have a
hard time conceptualizing one aspect of database design. I have a table
called accounts, and a table called logins. It is a one to many
relationship of course. First of all, am I right in suggesting their
are two ways to do this, one to have a Primary Key in Logins that
includes the primary key of accounts, and two, having a field in
accounts that references a Primary Key (say login_id) in the logins
table?
The part I am having trouble with, is say I have a table with 10,000
logins......sure I can easily find an accounts logins by doing a query
on an indexed field, which is part of( or maybe not) the Primary Key of
logins, and find every instance which corresponds to a certain
account_id, but that would seem to me..(Now here is the part I have
trouble metally with) that if I already am working with a certain
account_id, that account_id would have say maybe 50 logins to look
through. Why should I have to look through 10,000 or more records to
find those records? Even if it is indexed or whathave you.. Now, I know
that this is a comprehension issue probably, and the question sounds
stupid on the surface, and it doesn't fit in with my perception of
normalization rules and RDBM's, but somehow if I am manipulating a table
with 1000 accounts, is it not easier to do lookups of their accounts if
each had their own table of say 50 logins, rather than a common table of
50,000 logins each identified as belonging to a certain account_id?
What I am really looking for is to someone to explain this to me so that
it makes common sense to me, and still seems effecient.... I have read
all kinds of books, and am working with both ORACLE and POSTGRES, and
every time I design a database, this keeps giving me an eirie feeling
that something is not right...
Michael