Mysql Schema ...

[TylerNZ]

Hi, I was wondering if someone would be kinda enough to have a browse over my mysql database schema that I've drawn up. This is the 2nd one I've done up for this project ... a friend suggested some changes. So I've made them and this is what I've got so far.

the database schema (http://www.newlifechurch.org.nz/database.htm)

THANKS!!!!

[Hawkmoon]

Personally I would simplify it:

You really just need 3 tables:
members
groups
lifeteams

members would be:
memberID
teamID
groupID
firstName
lastName
email
phone1
phone2
(and whatever other info you wanted collected)

groups would be:
groupID
groupName
(and whatever group info you want...like group leader - which could be a memberID...etc)

lifeteams would be
teamID
teamName

What you have now is really great as far as dynamic and modular is concerned but the information would be very hard to mine for specific data in a lot of cases (ie Getting the email address of everyone in the Worship life team would be a pain) You'd have to get information from 3 different tables versus a simple statement like:

SELECT email FROM members WHERE teamID='7'; (in the case of tables above)

Just a thought...

[raf]

Hi, I was wondering if someone would be kinda enough to have a browse over my mysql database schema that I've drawn up. This is the 2nd one I've done up for this project ... a friend suggested some changes. So I've made them and this is what I've got so far.

the database schema (http://www.newlifechurch.org.nz/database.htm)

THANKS!!!!
Well, you can realy only evaluate a db-design if you know the dataprocesses it is used in and the overall aim of the application(s) that use it (--> flexability, speed, does it need to power a generic application, does it need to be modular in that respect that you need to be able to take out certain tables to migrate them to another db etc) I've made some weird, unconventional db-design because they best fitted my projects needs.
Db design is a lott more then then knowing something about db-normalisation and denormalisation.

If you're going to run a more conventional db-driven webapplication, then i'd use a simpler, normalised design like Hawkmoon posted, although i think you'll need to have a timedimension inthere (the attendance ?), which mean you'll need an extra factstable with

memberID | datevar or courseID or whatever | attendenceID

And then 1 or 2 extra dimensiontables --> one with the attendanceinfo
attendenceID | attendancelabel
and if required, one with the session/course/matchday info
courseID| datevar | teacherID

and if you have such a table with a teacher or a referee or whatever, then you'll have an extra table with the referees/teachers info etc

You see ? All the tables you frequently select on, then only contain numerical data --> primary keys and foreign keys, becasue a dataserver processes this sort of data the fastest. The actual, readable, humanly required data is then store in seperate tables that you only join with when required to output something on screen.

You'll see that having these ID's for each table will make it a lot easier to build your application since you only need to use he ID's to refer to the actual data, and it will be a lott easier to maintain, because each value (like a members name or whatever) only appears once inside the db, which makes it easier to update and manage.