I'm trying to figure out how to setup my first mySQL DB... I hope this
isn't too OT for this list.
The DB will serve several purposes, which I think will each go into a
different table w/in one DB.
Here's what I want to be able to accomplish:
pull up a listing of:
1) names and email addresses
2) names and real address
3) name and two-static (static per UID) URLs
4) Names of special people in their lives and events (for example, show me
the listing for "Tim Luoma" and all the special events he has entered and
who they apply to)
5) Pull out all special events of a given type (one of 4 pre-defined
types, i.e. show me all birthdays, show me all anniversaries, etc)
6) Pull out ALL special events for a given day (where "day" is a number
1-366 where 1 = Jan 01 and June 26 = 178, etc)
7) Pull up a listing of the entire year showing month, day, real name,
special event, and who the event is for, ie
may 28 Tim Luoma Ethan birthday
((that would show that Ethan's birthday was 5/28 and Ethan relates to Tim
Luoma))
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The DB will include:
1) Real name
2) Email
3) Address (some non-US)
4) several URLs (0-10)
[here's where it gets tricky for my brain]
5) a field for spouse/SO [may be empty]
6) A list of several special events in their life and the people they
apply to (*)
Re: #6.... for example, for me this would be
Tim Luoma
[EMAIL PROTECTED]
10 main st springfield ma 12345
http://url.to.somewhere
http://www.other.url
http://www.tntluoma.com
http://www.peak.org/~luomat/
Tracey (wife)
Ethan (son)
Tracey's birthday 1/5
Ethan's birthday 5/28
Our anniversary 6/17
My birthday 3/28
Some other special event 6/17
other event 2/13
other event 12/17
Now the trick, to me, is that there may be any number of special events
and any number of people they may apply to. One person may have 10,
others may have 1, a very few might have none (grrrr.... ) They also may
not have a spouse or a child, or they might have 10 (it could happen).
And some of the events may be on the same date (rare but possible). They
also may not all have homepages or other URLs that I need to store.
Now from my feeble understanding of how this all works, I need one main DB
with several tables. Each person should have a unique ID (3 digits
enough? There are only 50 people I'm dealing with right now and in my
wildest dreams I can't imagine it ever breaking 1000 people... we're a
small, closed mailing list and we argue whether or not to go to 55 over 53
members.)
Anyway, tables, as I see them, might break down this way:
mainTable
UID
Real Name
Email Address
locationTable
address
location url1
location url2
familyTable
(for each person in familyTablethere would be up to 0-4 events for each
person: bday, anniv, adopt, rb)
Spouse
other01
other02
other03
other04
other05
other06
other07
other08
other09
other10
other11
other12
other13
other14
other15
other16
other17
other18
other19
other20
[now very few people would have that many, but it's enough that no one
should have more]
For example, for me it would be:
bday anniv adopt rb
3/28 6/17
another entry might be:
bday anniv adopt rb
2/13 12/15
Does this sound logical? Am I missing anything?
TjL, still waiting for Amazon to deliver his copy of the Welling & Thomson
book :-)
--
Timothy J. Luoma
mailto:[EMAIL PROTECTED] http://www.tntluoma.com
mysql Ver 11.17 Distrib 3.23.49a, for pc-linux-gnu (i686)
Apache/1.3.26 (Unix) PHP/4.2.1 mod_gzip/1.3.19.1a
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
