Table design question (to foreign key or not to foreign key)

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


0
Matt
3/12/2002 11:46:20 PM
sybase.ase.general 8655 articles. 0 followers. Follow

9 Replies
682 Views

Similar Articles

[PageSpeed] 57

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
0
Jim
3/13/2002 5:48:51 AM
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


0
Matt
3/13/2002 6:40:15 AM
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


0
BEDick
3/13/2002 3:37:59 PM
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



0
Matt
3/13/2002 6:00:30 PM
"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



0
BEDick
3/13/2002 11:39:29 PM
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


0
Matt
3/14/2002 12:00:56 AM
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
0
Jim
3/14/2002 4:34:39 AM
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


0
Matt
3/14/2002 6:21:24 AM
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


0
Matt
3/14/2002 6:21:24 AM
Reply:

Similar Artilces:

Attempt to over-write foreign key column "Code" where table A has 3 foreign keys to table X
Generating from CDM to Logical. PD 9.5.2.889 This is a weird one - 4 tables in my model have more than one foreign key reference to table COROrg. By default a foreign ket to COROrg generates as COROrgKy. I have renamed the other foreign keys appropriately - e.g. COROrgKyEmpGrp. After the rename when I generate from CDM to Logical everything's fine for all but on of the foreign key columns (i.e. the new name/code are maintained in all but one). The list of changes to happen (obtained from Merge dialog -> Merge Preview) says it's going to 'Modify Column "...

Foreign key to other table, with foreign key back to first one: ok?
Is it ok for two tables to have foreign keys to each other based on the same columns? If so, are there rules about the cascade settings in such a case? I am thinking of tables that are virtually one-to-one with each other and can tend to be viewed or deleted equally "from either end". -- Steve http://www.geocities.com/SiliconValley/Sector/6376 The first problem I see with this is that it is a "which came first, the chicken or the egg" type of a problem. When you insert into one table the FK is going to want to check to see if the value is in the other...

Table design and Foreign Keys
G'day guys,I am writing a small project - of which I allow the user to select the country, and then the state in which they live. Some 'states' though, are sub-divided. For example, the United Kingdom has Scotland, England, Ireland, Wales. These are then sub divided into smaller 'states'. However, South Africa has Western Cape, Gauteng etc.. and that's their smallest division. So, I have 3 tables. Country, which has many Areas, which States.In the UK example, United Kingdom is the country, which has a few 'Areas' (Scotland, England etc), and then each Are...

integer primary/foreign key vs. varchar(255) primary/foreign key
Hello. Is there any principal performance (or some other) difference between using the integer primary/foreign key and using the string (varchar(255)) keys in queries that use tables joins? Server is Sybase SQL Anywhere 5.5.04 Yes! The primary key will only be made up of 12 bytes. By using an integer you ensure that your entire column is in the key. -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX *********************************************************** Enterprise Applications in Your F...

Updating tables in sequence with primary key and foreign key relations
Hi all,        In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations        Right now my code looks like this        foreach(object obj in Objects){        int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table        int DebtId=Debt....

Primary Keys containing common foreign keys from different tables
I have 2 tables Table 1 and Table 2 which have primary keys which include the same foreign key from a third Table 0. Table 1 has a primary key consisting of T1_KEY<pk> and T0_KEY<pk,fk>. Table 2 has a primary key consisting of T2_KEY<pk> and T0_KEY<pk,fk>. Now I want Table 3 to reference both Table 1 and Table 2 so that it has a primary key consisting of: T3_KEY<pk>, T0_KEY<pk, fk>, T1_KEY<pk, fk>, T2_KEY<pk, fk> I am trying to manually reverse engineer a database for which I only have a verbal description. I manage to achieve ...

Two foreign keys to same table (LINQ question)
Apologies if this is the wrong place to put this, this forum needs a LINQ section!I have a table, Activities.  Another table, ActivityPredecessors links an Activity with predecessor Activities.  Working in Visual Studio 2008 using LINQ to SQL, I can usually access the contents of the table related by foreign key by using the foreign table's name (eg Activity.Group.grp_name).  How should I do this in the case where the table which contains foreign keys has more that one foreign key to the same table?  Will I need to write custom queries rather than using the built-in L...

Primary Key/Foreign Key
Can I use SQL Commands to add Primary key or reference keys to existed tables? (Not a new table, that's why I can't use CREATE TABLE command.) If does, could someone give me some sample codes? Thanks. Hi John, What you need is the ALTER TABLE statement. Look for it in SQLA help for the syntax. Regards, Nat. John Chyan wrote in message <35A4FF5D.5F192806@dissol.com>... >Can I use SQL Commands to add Primary key or reference keys to existed >tables? (Not a new table, that's why I can't use CREATE TABLE command.) > >If does, could someone ...

primary key and foreign Key????
 Hi i have 2 table.. Table1UID         Username1               AB2               CD3               DD Table 2ITEM       Price          UIDPOO1      20.00          1POO2      10.00          2POO3       15.00         1 Now i am letting the Admin to delete the user in table1But since UID 1 and 2 are ...

SQL statement failed, no primary key value for foreign key ... in table ...
Problem: SQL statement failed, no primary key value for foreign key ... in table ... After deleting or changing primary key values in either consolidated or remote database. Question: Are there any triggers that can resolve inserting a record that has a foreign key value with no existing primary key value in primary table? Description: We have a serious problem with the dbremote program. I think it's a general problem that could appear in every database with related tables. Everytime the problem turns up we must create new remote databases from the consolidated datab...

Primary key
Hello, I've got a modeling problem betwin a conceptual model and a physical model and espacially about the foreign key. See below the illustrated exemple of the problem : -Whether the entity � ARTICLE � with two informations : CODE & WORDING -Whether the entity � UNITE � with two informations : CODE & WORDING An article has necesseraly three unity, the unity of command, of storage and of sails. These three unity can be differents. Conceptual model phycical model The generated physical model doesn't suitable me. How ...

foreign key as a primary key
hi, I am using a foreign key (which is a primary key in a secondary table) as a primary key in a primary table. There is also another primary key in the primary table. Do You have to declare the foregin key as a primary key ? regards Jan Eikeland email:jaeikela@c2i.net url :http://home.c2i.net/jaeikela In article <368b0234.5056065@forums.powersoft.com>, jaeikela@c2i.net says... > hi, > > I am using a foreign key (which is a primary key in a secondary table) > as a primary key in a primary table. > There is also another primary key in the primary table...

Primary key/Foreign key again
Thanks, Nat and David. Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command to add my primary key: ALTER TABLE Client ADD PRIMARY KEY (SYSID) I got an error: user 'dba' has the row in 'Client' locked. How can I release the locking condition? Click on another table in Sybase Central. Is suspect this is the issue. Dave Wolf Jaguar Product Team John Chyan wrote: > Thanks, Nat and David. > > Under "SQL Anywhere", I get into ISQL to use "ALTER TABLE" command > to add my primary key: > ...

AutoUpade UserId [Foreign Key] From the Primary Key UserId in the aspnet_Users table ?
 Hello everyoneI want the userId PrimaryKey in the aspnet_Users Table in the ASPNETDB.mdf  to Update it's foreign key in another table called ClientInfo in the same database, Once the User is created a new userId is created, i want this userId to be automatically entered in the foreign key table ClientInfo...how can that be accomplished?  You can have a trigger on aspnet_Users table doing the insert into the ClientInfo table.Regards,PrashantDont forget to click "Mark as Answer" on the post that helped you.  thanks for the fast reply..it would be great i...

Web resources about - Table design question (to foreign key or not to foreign key) - sybase.ase.general

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Iran's Revolutionary Guards question US sailors, dismiss talk of prompt release
Iran's Revolutionary Guards dismiss talk of the prompt release of 10 US sailors as speculation after their arrest on Wednesday.

Question Of The Day: What's your car for the Year of Your Birth Rally?
Filed under: Automotive History Born in 1927? You can drive a Ford Model A in this rally! Born in 1996? Subaru SVX! Continue reading Question ...

A tech veteran says investors shouldn't always trust their gut — they should ask these 5 questions instead ...
Aileen Lee, a longtime tech venture capitalist , says there's no formula for assessing whether or not you should bet on startup founders. "I've ...

Cheers, jeers and questions abound with Rams return to L.A.
Rams, Chargers and Raiders face serious challenges and the city of St. Louis is distraught after losing its team

Wendie Malick Weighs in on Jerry Hall's Engagement to Rupert Murdoch: 'I Question Her Choice of Husbands-to-Be' ...
Wendie Malick Weighs in on Jerry Hall's Engagement to Rupert Murdoch: 'I Question Her Choice of Husbands-to-Be'

5 Questions For 5 Rising YouTube Musicians: Everyll
VideoFrom its early days, YouTube proved to bea good source for lots of music.Most people know the big stories emerging from this confluence—Bieber, ...

Ruling Puts Fate of Florida Death Row Inmates in Question
Condemned killer Michael Lambrix is scheduled to be executed next month, one of hundreds of Florida death row inmates whose fate is now in question ...

No Woman Should Be Asked These Questions During A Job Interview
Attention hiring managers: Don’t ask a woman about her marital status, plans on having kids or her child-care arrangements during a job interview. ...

Quick Question: What's Your Personal Worst Car-Related Idea?
I love terrible ideas. Maybe it’s because I have so very many of them, but I’m really fond of an idea that seems iffy, and then just gets more ...

Resources last updated: 1/14/2016 1:52:40 PM