I know there are a million schools of thought on design structure, so
I'm not trying to argue here, but I'd like to make some comments.
> A few comments on Trevor's DB structure recommendations regarding
> primary keys.
>
> 1) Only make the auto-incrementing primary key IF you don't
> already have
> a unique identifier for each item in the table. Your church probably
> doesn't have existing unique IDs for each member (or maybe you do -
> membership #?), so that's ok for the "users" table. For "groups",
> though, presumably you don't have two groups both called
> simply "choir",
> so you might as well make the group name the primary key there.
I never assume that users (if they have a hand in naming things like
membership groups) will NOT pick something that's duplicate. Using an
ID column removes all doubt. Plus, for doing the cross ref with the
users who belong to that group, I'd rather have a couple of numbers
stored than a string (potentially long) of the group name.
> 2) Along the same line, I'd say you don't need an auto-incrementer in
> the "memberships" table. What you do there is make the
> primary key span two columns, which tells MySQL that each user/group
pair must
> be unique.
This is a valid point. I'm not used to doing multi-column primary keys,
but I understand the idea. Thanks for mentioning it. You should never
end up with a member being in the same group twice, so this should work
out ok.
My only concern (and this would be more of a concern if you were dealing
with more than a few thousand rows of data) is that when you reference
the table, concatenation functions to compare the columns as a single
unit usually slow down a process quite a bit.
For example, we deal with truckers and airline freight where I work
(air-cargo-inc.com.. Obvious eh?) and one thing we deal with are
AirWaybills (AWBs). They consist of a three digit airline code ("057"
for Air France, etc) and an 8 digit serial number for that AWB. So they
might look like "057-01234567". In most of our databases, it's stored
like that. In one, it's stored as AirlinePrefix and SerialNumber. So
to compare a regular table's "057-01234567" with "057" and "01234567"
you'd need to do "WHERE t1.AWB =
t2.AirlinePrefix+'-'+t2.SerialNumber" which can produce a fairly slow
query with the amount of data we have in our system.
In that case, we don't really have a choice until we get our DBA to
re-engineer everything.
After all that babble, I do have to say that that's not really a concern
in this case because you're not dealing with that many people and saying
"DELETE FROM SomeTable where MemberID = 6 and GroupID = 3" isn't that
hard.
So, point stands. Using the member id and the group id should work
great for a primary key.
> 3) I fail to see why the "attendance" table needs a primary
> key at all.
>
> As a general rule, I say a table only should get a primary key if
> something in there, be it a single column or combination of columns,
> must be unique in each row. Otherwise your tables are storing
> data that isn't used.
I like using an autonum/identity column in most of my tables (except
xref type tables, they usually don't need it.. As mentioned above)
because it makes it easier to pull out the EXACT record (mostly for
things like DELETEing and UPDATEing... Or if you need to do something to
the record right after it's INSERTed like have a trigger make an entry
into an xref table.. Then you'd want to snag the newly created ID).
If there's an autonum column where it's impossible, due to database
rules and restrictions, to have duplicates, it removes all doubt for me.
And when you're talking about 2000 members, the overhead of an autonum
column is pretty negligible I think.
> You might also consider removing the "memberships" table entirely in
> favor of a comma-separated BLOB field "memberships" in the "users"
> table; MySQL's FIND_IN_SET function works well to process
> data like that in queries. The upshot is that you're reducing
> table size (I think) and making it easier to make a list of members
> that users belong to, since MySQL 4.1, with its GROUP_CONCAT function,
> isn't released yet. The downside is that it's not quite as "pure" a
> data layout, and there probably are portability concerns as well.
> Of course, were you using Postgres, you could just make an array-typed
> column. ;-)
Hah.. Well, that's the problem sometimes with these great functions.
You use them, get used to them, then move to another DB system and
you're like "uhh.. How come this doesn't work anymore?" I'm not
totally a purist about things, but I believe in reducing headaches or
potential headaches. To me, having the Memberships xref table is my
preferred way of tackling this one because it should be happy in any DB
system. Most DBs have some kind of strstr type function to find things
within a string in a datdabase field, but those kind of operations can
be slow and it means figuring out what commands work with what DBs.
Select u.Username,g.GroupName
>From Users u, Groups g, xrefUsersGroups xr
Where u.UserID = xr.UserID
And xr.GroupsID = g.GroupsID
And u.Username = 'Trevor Gryffyn'
Real simple.. Works both ways too:
Select g.Groupname,u.Username
>From Users u, Groups g, xrefUsersGroups xr
Where u.UserID = xr.UserID
And xr.GroupsID = g.GroupsID
And g.GroupName = 'PHP Users'
> Just my $.02; the rest of the suggestions look fine to me.
I see your $.02 and raise you a nickel :)
Good thoughts though, it's always good to have options and know what
they are. Thanks for posting, Felipe. I disagree for the above
reasons, but a lot of this is personal preference. If he's never going
to use anything but MySQL, then there's definitely no harm in leaning on
functions specific to MySQL. I end up using many different kinds of
databases, so I try to keep my practices a little more universal (so I
don't cause myself headaches), but that's my style. Your ways work just
as good.
You could have just one big table with a column that just had a list of
groups the user was in:
"Trevor Gryffyn", "PHP Users, DB Nazis, Mazda Drivers", "Etc"
And that'd work just fine too. It's not generally considered "good
database practice" and may be more cumbersome to manage than "better"
ways, but you could still make it work.
Anyone else want to chime in? Haha..
I never went to school for any of this, I'm all self-taught. So
sometimes my ways are goofy but I always try to learn and improve. If
I'm being goofy, forgive me. Hah
-TG
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
