Our application is a message board and is full of messages, threads, and
forums.  A message is an individual entry (like the one I'm typing now ;)).
Our software gives the user the ability to upload a single file attachment
to each message.

I can think of a couple different ways to design the two tables:
message( messageid numeric identity, attachmentid numeric null, messagetext
text, other, message, stuff... )
attachment( attachmentid numeric identity, title varchar, other, stuff )

or

message( messageid, messagetext text, other, stuff )
attachment( messageid numeric foreign key to message( messageid ), title
varchar, etc. )

The problem comes into play in that in the application 'front end' we need a
graphical indication that the post has an attachment (a little paperclip and
a 'click here for the attachment: attachment.title') type thing.  So, I need
to do something nifty to pull back whether or not an attachment exists, and
if so, grab the title from it.

Currently our messages table is 3.2 million rows; and grows at a rate of 2
to 3 million or so a calendar year.  We do not 'clean' out the post table.

So the question is -- will the 'attachmentid' in the 3+ million row post
table take up an appreciable amount of space (yeah 4 bytes * 3 million, but
it also widens the row, plus an index on message.attachmentid in the first
instance -- because if the attachment is deleted I need to null-out the
message.attachmentid column fk).

I figure the second method will save space since I do not have an extra 4
bytes per row plus I save another index, however I'd have to JOIN them
together to see if they have an attachment at all (whereas in the first
method I could see if attachment ID was not null, if so then go ahead and
pull the title back from the table).

However, if I 'copy' a post (which can be done with the software) in the
second method I would have to make a physical copy of the attachment since
messageid is the primary key, whereas in the first method I could simply
create a new message with an attachmentid which points to the already
existing attachment.

I could solve multiple-posts per attachment like this:
message( messageid ident )
attachment( attachmentid ident )
message_attachment( messageid, attachmentid ).

But that would make a 3 table join necessary:
select ...
  from message,
      attachment,
      message_attachment
 where message.messageid = message_attachment.messageid
     and message_attachment.attachmentid = attachment.attachmentid

And also the problem arises in that we can either delete the attachment *or*
the post, so I'd have to have indexes on both attachmentid and messageid in
the attachment table. :(

Currently I'm leaning toward the second method, namely have attachment's
primary key be the messageID from the message table and simply JOIN them
together (since they will both be clustered I figure it will be very cheap
to do so, I could make the attachment primary key be attachmentid, title to
read only the clustered index and not the data row).  That would avoid
delete anomalies and save space at the expense of creating a new attachment
if I copy a post.

Any thoughts?

Thanks,

--
Matt

matt@fanhome.com wrote...
> Our application is a message board and is full of messages, threads, and
> forums.  A message is an individual entry (like the one I'm typing now ;)).
> Our software gives the user the ability to upload a single file attachment
> to each message.
> 
> I can think of a couple different ways to design the two tables:
> message( messageid numeric identity, attachmentid numeric null, messagetext
> text, other, message, stuff... )
> attachment( attachmentid numeric identity, title varchar, other, stuff )
> 
> or
> 
> message( messageid, messagetext text, other, stuff )
> attachment( messageid numeric foreign key to message( messageid ), title
> varchar, etc. )
> 
> The problem comes into play in that in the application 'front end' we need a
> graphical indication that the post has an attachment (a little paperclip and
> a 'click here for the attachment: attachment.title') type thing.  So, I need
> to do something nifty to pull back whether or not an attachment exists, and
> if so, grab the title from it.
> 
> Currently our messages table is 3.2 million rows; and grows at a rate of 2
> to 3 million or so a calendar year.  We do not 'clean' out the post table.
> 
> So the question is -- will the 'attachmentid' in the 3+ million row post
> table take up an appreciable amount of space (yeah 4 bytes * 3 million, but
> it also widens the row, plus an index on message.attachmentid in the first
> instance -- because if the attachment is deleted I need to null-out the
> message.attachmentid column fk).
> 
> I figure the second method will save space since I do not have an extra 4
> bytes per row plus I save another index, however I'd have to JOIN them
> together to see if they have an attachment at all (whereas in the first
> method I could see if attachment ID was not null, if so then go ahead and
> pull the title back from the table).
> 
> However, if I 'copy' a post (which can be done with the software) in the
> second method I would have to make a physical copy of the attachment since
> messageid is the primary key, whereas in the first method I could simply
> create a new message with an attachmentid which points to the already
> existing attachment.
> 
> I could solve multiple-posts per attachment like this:
> message( messageid ident )
> attachment( attachmentid ident )
> message_attachment( messageid, attachmentid ).
> 
> But that would make a 3 table join necessary:
> select ...
>   from message,
>       attachment,
>       message_attachment
>  where message.messageid = message_attachment.messageid
>      and message_attachment.attachmentid = attachment.attachmentid
> 
> And also the problem arises in that we can either delete the attachment *or*
> the post, so I'd have to have indexes on both attachmentid and messageid in
> the attachment table. :(
> 
> Currently I'm leaning toward the second method, namely have attachment's
> primary key be the messageID from the message table and simply JOIN them
> together (since they will both be clustered I figure it will be very cheap
> to do so, I could make the attachment primary key be attachmentid, title to
> read only the clustered index and not the data row).  That would avoid
> delete anomalies and save space at the expense of creating a new attachment
> if I copy a post.

I prefer the second method, but it is less flexible if you truly can attach a single 
attachment to multiple messages.  That's odd.  I can't see how that would take place.  But 
if you did need something like that then this would work:

Message (messageId INTEGER PRIMARY KEY, ...)

MessageAttachmentXref (messageId INTEGER, attachmentId INTEGER)

Attachment (attachmentId INTEGER PRIMARY KEY, ...)

A lot of Sybase shops use Replication Server to ensure minimal downtime.  Identity columns 
are not something you want in a replication scheme.  If replication is at all a possibility 
then I strongly suggest you do not use Identity columns.  Instead, create a table of keys 
and use a stored procedure to generate keys from it.
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Jim,

Thanks for the reply.  I also like the second method better and I think I'm
going to use it.. if someone copies a thread with attachments intact then
we'll just have to duplicate the attachments which sort of sucks.. maybe
I'll force it to always 'discard' the attachments.  People usually copy
threads to an admin area to which we can view and keep it in isolation
(should it need editing for objectionable content we'd like to have the
original around)... I can't think when an attachment would need to be
copied.. :)

Re: Ident cols yes replication *could* be a feature although I can't squeeze
any pricing out (nor that demo copy! :(   ).  We're currently already in
production with ident columns and re-designing a lot of the backend.  Would
you suggest one of the methods located here:
http://my.sybase.com/detail?id=860

Or do you have perhaps a better idea?

Thanks again,
--
Matt

"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.16f89630cd03514598bbc1@forums.sybase.com...
> I prefer the second method, but it is less flexible if you truly can
attach a single
> attachment to multiple messages.  That's odd.  I can't see how that would
take place.  But
> if you did need something like that then this would work:
>
> Message (messageId INTEGER PRIMARY KEY, ...)
>
> MessageAttachmentXref (messageId INTEGER, attachmentId INTEGER)
>
> Attachment (attachmentId INTEGER PRIMARY KEY, ...)
>
> A lot of Sybase shops use Replication Server to ensure minimal downtime.
Identity columns
> are not something you want in a replication scheme.  If replication is at
all a possibility
> then I strongly suggest you do not use Identity columns.  Instead, create
a table of keys
> and use a stored procedure to generate keys from it.
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services

Your first approach is a many-to-zero model (1). Your second approach is a
zero-to-many model (2). And with the right primary key, your third approach
is a many-to-many (3).

1) Many messages may share an attachment.
2) A message may have many attachments.
3) Both 1) and 2) are true.

Which is it logically? Once you figure that out, then you worry about
performance.

--
Later,
BEDick

We're still in the planning stages, so I can limit functionality to achieve
performance (as Michael Peppler says ;)).

So I'd like to say it would be a one-to-one relationship but of course not
every post has an attachment but every attachment must match to a post.

--
Matt

"BEDick" <bdick@cox.net> wrote in message
news:JwlpSWqyBHA.318@forums.sybase.com...
> Your first approach is a many-to-zero model (1). Your second approach is a
> zero-to-many model (2). And with the right primary key, your third
approach
> is a many-to-many (3).
>
> 1) Many messages may share an attachment.
> 2) A message may have many attachments.
> 3) Both 1) and 2) are true.
>
> Which is it logically? Once you figure that out, then you worry about
> performance.
>
> --
> Later,
> BEDick

"Matt" <matt@fanhome.com> wrote in message
news:$A93XiryBHA.206@forums.sybase.com...
> We're still in the planning stages, so I can limit functionality to
achieve
> performance (as Michael Peppler says ;)).
>
> So I'd like to say it would be a one-to-one relationship but of course not
> every post has an attachment but every attachment must match to a post.

To get a one-to-one put a not null message id with a unique constraint in
the attachment table.

--
Later,
BEDick

That's what I got!  :)

Thanks again,

--
Matt

"BEDick" <bdick@cox.net> wrote in message
news:J5iiXjuyBHA.133@forums.sybase.com...
> To get a one-to-one put a not null message id with a unique constraint in
> the attachment table.
>
> --
> Later,
> BEDick

matt@fanhome.com wrote...
> Re: Ident cols yes replication *could* be a feature although I can't squeeze
> any pricing out (nor that demo copy! :(   ).  We're currently already in
> production with ident columns and re-designing a lot of the backend.  Would
> you suggest one of the methods located here:
> http://my.sybase.com/detail?id=860
> 

The Next Key Table is one that I have worked with before.  Should it be necessary, you can 
change from using Identity to a simple INTEGER data type.  
-- 
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services

Sounds interesting.  I'll certainly keep it in mind if we even consider
replication server. :)

--
Matt

"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.16f9d64a1d29e2eb98bbd8@forums.sybase.com...
> The Next Key Table is one that I have worked with before.  Should it be
necessary, you can
> change from using Identity to a simple INTEGER data type.
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services

Sounds interesting.  I'll certainly keep it in mind if we even consider
replication server. :)

--
Matt

"Jim Egan" <dontspam.dbaguru@eganomics.com> wrote in message
news:MPG.16f9d64a1d29e2eb98bbd8@forums.sybase.com...
> The Next Key Table is one that I have worked with before.  Should it be
necessary, you can
> change from using Identity to a simple INTEGER data type.
> --
> Jim Egan [TeamSybase]
> Senior Consultant
> Sybase Professional Services