Stuart,
You need to begin to divorce these two processes from each other: STORAGE
and INPUT/OUTPUT. What your user's see on a web page doesn't need to look
anything at all like your database structure. A user interface (UI) is
designed for ease of use. The other is designed for query and storage
efficiency and there are frequent occasions where the two structures are
radically different.
You need to store information on two entities (sometimes also referred to
as objects) that are related in a many-to-many relationship: any ONE
"member" is related to several (or zero) "titles" and any "title" can be
used by several (or zero) "members". To represent this in the database
you typically need 3 tables, one for the "member"-specific information,
one for the "title"-specific information, and one carry information about
each "member"-"title" association.
CREATE TABLE member (
MemberID int auto_increment
, Login varchar(25) not null primary key
, Name varchar(30) not null
# , (other fields as necessary)
, KEY(ID)
)
CREATE TABLE title (
TitleID int auto_increment
, TitleName varchar(25) not null primary key
# , (other fields as needed)
, KEY (ID)
)
CREATE TABLE member_title (
ID int auto_increment
, member_ID int not null
, title_ID int not null
, startdate datetime
, enddate datetime
, KEY(ID)
)
So let's say you have only 2 members:
INSERT member (Login, Name) VALUES ('jblow', 'Joseph Blow'), ('msmith',
'Mary Smith');
Between them they have held the titles of "Flunky", "Gopher", "Assistant",
"Intern", "Scumbag", and "Vice-President" (as we are all aware, frequently
a few total idiots do make it into senior management). So let's add those
titles to our "list of titles"
INSERT title (Titlename) VALUES ('Flunky),
('Gopher'),('Assistant'),('Intern'),('Brownoser'),('Vice-President');
Here is where the rubber meets the road. Who held which position and
between which dates?
INSERT member_title (member_ID, title_ID, startdate, enddate)
VALUES (1,1,'2004-02-01', '2004-03-01')
, (1,2,'2004-03-01', '2004-03-15')
, (1,3,'2004-03-15', '2004-04-01')
, (1,6,'2004-04-01', '2004-04-02')
, (1,3,'2004-04-02', null)
, (2,5,'2003-11-15', '2004-01-01')
, (2,1,'2004-01-01', '2004-03-01')
, (2,3,'2004-02-01', '2004-08-10')
, (2,4,'2004-08-10', null);
Can you see how this table contains a running history for each "member"
and what "title" they held and when they held it? I left the enddates
empty (null) because they haven't been moved from those positions. There
is one case where one of your "member"s held two "titles" at the same
time. Can you spot it?
If you would like more details, just ask. This was only a summary of how
to store information in a many-to-many structure. I would have given you
more examples but I am a little busy today and couldn't take all of the
time I wanted to spend on this topic (sorry!)
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 08/24/2004 06:21:58 PM:
> Trying to figure out how this gets done. Let me
> explain first. By the way I hope this is not off topic
> as perhaps it is more towards application then core db
> .
>
> I have a table:
>
> Count[int][auto-increment], MemberID[int],
> Title[varchar], TitleYear[int]
>
> In my organization members can have or have held
> various titles. I'm trying to collect, right now, 5
> titles and the years they've held those titles.
>
> Extraneous - Now I'm in a precarious place, cause I am
> new to web dev and db and have had the fortunate
> experience to be using an extension to dreamweaver
> that makes it relatively simple. Until you have to go
> beyond what they provide in functionality /
> capability. It's php / adodb.
>
> So I"ve looked around and found a few "methods" but no
> detail, nor have I really drilled down on how they get
> implemented.
>
> One method was basically a loop of sorts. Not
> entirely sure but I suppose pages can keep refreshing
> 5 times.
> Second, was to pass all the variables over to another
> page. It sounded like maybe this secondary page is
> hidden but set up to accept an insert. The third,
> which I tried, unsuccessfully, was through the use of
> after triggers. Possible I need to work on this more.
>
> Anyway I wouldn't mind hearing how other people deal
> with this issue. Not sure if I could set up some SQL
> statements.
>
> Thank you ,
> Stuart
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
