I did that as a convenience. Basically it provided me two ways of uniquely
identifying any row in a table(an auto_inc integer, and some unique text
column). The autoincremented values should never repeat. HOWEVER because I
did not put a UNIQUE constraint on them, it is possible for someone to
come back later and change one to match another. In a production
environment, I would have created the UNIQUE constraint. The other thing
that did for me is to provide numeric values for my table relationships.
Otherwise I would have had to duplicate my primary keys (text columns) in
every child table. Having all of that text in the database multiple times
would take up much more room than just the integers.
You are correct in saying that I could have written my tables to look
like:
CREATE TABLE member (
ID int auto_increment primary key,
login varchar(25) not null,
... other columns...
, UNIQUE(login)
)
and achieved the same basic structure.
You asked about the "wisdom" of normalizing on "Titles". If you want each
person to have their own list of titles, you are in a one-to-many
situation. Between lists, titles can duplicate but each person will have
their own list. This structure requires only 2 tables, "member" and
"title". The difference is that you put a member_id column on the "title"
table so that you can keep up with which titles a person has held and for
what dates. Otherwise you could have a blank field on just the "member"
table and the user is limited to only 1 title, ever. The problem with the
single-field design is that you would not have any history of the other
titles a person held because every time the title changes, the old title
is wiped out and replaced by the new one. With the two-table model, you
will be able to keep a running history of all titles held by a member.
Which model you choose depends on your application needs and what you need
to do with the information.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Stuart Felenstein <[EMAIL PROTECTED]> wrote on 08/25/2004 05:11:07 PM:
> Too funny! I'm slowly coming to grips on the M2M. I
> decided not to opt for it in this situation as I still
> believe it would not address my issues.
>
> My problem, or a better to phrase it , my solution is
> that I am not supplying titles. They are, to the user
> , blank fields , left to them to supply their title.
> Now I may have missed your point. Though it was 1-3
> that held the same title twice. That much I grasped.
> Anyway, because there are literally a few thousand
> titles I'd need to insert I opted not to at this point
> unless or until I start seeing a trend of common ones.
>
>
> Does this make any sense ? Please tell me if I'm
> wrong
>
> Also another quick question:
> In your example tables I noticed you have an ID for
> the member plus a Primary Key ID ?
> Is this also common ? In particular with my tables for
> members I have 1 primary key column which is also the
> member ID .
>
> Is that okay ?
>
> Stuart
> --- [EMAIL PROTECTED] wrote:
>
>
> > 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)
> > )
> >
>
