<lurk, lurk> -- yep, I'm hanging around ;-)
You don't have a many-to-many relationship between resumes and members.
It's a one-to-many (each member can have multiple resumes but each resume
belongs specifically to only 1 member)
That's done with a single table like this (not 3 different resume tables):
CREATE TABLE resume (
ID int auto_increment not null,
member_ID int,
...more fields to hold the resume content and metadata ...
)
The member_ID can appear multiple times in the resume table but each
resume is limited to only 1 member_id (one-to-many).
Before you even allow a user to attempt an insert a new resume into your
system, check to see if they have already met their limit:
SELECT member_ID, count(1) as rescount
FROM resume
WHERE member_ID = ### <-- put a real number/string there
GROUP BY member_ID;
And compare the results of the 'rescount' column to your business rule
(some day you may allow 6 resumes, who knows?). This comparison happens in
your application code, not the database, as you need to decide whether to
give the user the screen to enter another resume or to send the user a
screen that says "So sorry but you already have your limit of resumes on
file".
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
------8<---- prior responses mangled for space ---8<--------------
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 09/02/2004 01:05:31 PM:
> I have a table that hold resumes , each member can
> store 3 resumes.
--8<--
> ResTable1
> .......(same fields as above)
> ResTable2
> ........(yadda yadda)
>
> Member_ResTable
> Member (well actually not sure what would be in here)
>
> Shawn, if your out there - I'm not ignoring your
> advice to stave off the coding for now , concentrate
> on the db design. I just haven't come to grips with it
> yet! LOL
>
> Stuart
--8<--
