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.
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.
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.
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. ;-)
Just my $.02; the rest of the suggestions look fine to me.
-Felipe Gasper
Quoth Gryffyn, Trevor on 10/15/2004 11:10 AM...
Table 1: Users This contains all your user information. The first column in most database tables (there are exceptions) should always be a primary key that's automatically incremented each time a record is added. This creates a unique identifier for each record. I forget what they call it in MySQL, maybe AutoNumber (on an INT or INTEGER data type). In MS SQL Server they call it "identity". Columns recommended: UsersID Name Address Phone etc...
Table 2: Groups This is where you hold some basic (or detailed) information about the groups that meet. Same deal with the identity/autonumber column Columns recommended: GroupsID GroupName GroupLeader MeetingLocation (might be another ID field that points to a locations table.. Or locatio may be stored on a date by date basis if it is possible that it moves)
Table 3: Memberships This is a cross ref table that you store the identity/autonum values for a user and a single group that they below to. There will be multiple entries if a user is in multiple groups. This probably only needs to consist of 3 columns: ID (identity/autonum), UserID, GroupID Columns recommended: MembershipsID UsersID GroupsID
Table 4: Attendance After your identity/autonumber ID field, you'd want a date, groupid and userid. You could have other info too like Topic of the meeting or guest speaker info or whatever. If you get too much info, you might want to store it in another table with a link back to the identity column of this table Columsn Recommended: AttendanceID Date UsersID GroupsID
Depending on how anal you are about normalization, you might have an xref table that links groups and users with attendance and only have meeting date and location (and other such unique data) in the Attendance or maybe a "Meetings" table.
From there, you just need to learn how to do some SQL to join the tablesin order to retrieve the data.
The other things on your task list would be:
1. Create an interface 2. Learn how to insert data into the database (keeping all the tables straight) 3. Probably learn how to import an existing list of your 2000 members into the database to save you some typing 4. Create reports based on the data you pull showing "last attended this group" for each user and flag it if it's over a certain amount of time.
You'll find some good guideance on these groups (you might check out the PHP DB one for the database questions, but I'm sure someone on here could help as well).
Anyway, hope that gives you an idea of what you're getting into. It's really not all that bad, but it'll be a challenge for a newbie :) A good challenge though, not one of those TOO frustrating ones.. Heh
Good luck!
-TG
-- Quidquid latine scriptum sit altum viditur. Si hoc legere scis, nimis eruditionis habes.
Easier web browsing: http://mozilla.org
-- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
