Design Question 1

[megmogg]

Hi all

We have a table that logs client communications. This needs to be altered to log additional followup communications which are linked to the original communication.

Communication Table

communicationid (primary)
contactid (foreign)
overview 
date

The question is, do we create a new table to record the follow ups or do a self join.

The advantage of the self join is because all the fields will be the same as the communication table and also a query to find follow ups or communications would be simpler.

The advantage of having two tables would be the table size.

Anyone any suggestions?

 

[DomTrix]

I would add an extra field called [bold]originalComID[/bold]. If the communication is a new one, originalComID will be set the same as communicationid. If it is linked to another communication, it will be set to that communication's ID. (The new field would be a foreign key linked with the primary key in the same table).


DT

 

[megmogg]

Thanks for the reply.

That was my original thinking, but was worried the table could get very big as there could be numerous follow ups to one communication.

Any other thoughts?

 

[DomTrix]

Sry, just noticed that you already suggested that in clearer words lol. But yes, I would go with the inner join simply because I like less tables, but Im not sure thats the greatest reason. But unless the single extra field is going to dramatically increase the size of the db (so that it becomes a problem) I see no reason for two tables.

dt

 

[DomTrix]

What type of field is the ID? If it is an integer, then you are only adding 4 bytes to each record. Multiply that by the number of records you have and expect to have in the life of the table and you should get a rough estimate on the space you'll need.

 

[megmogg]

Thanks for the replies.

Yes, the ID is an auto-incrementing integer. I'll work it out.

Can the table have a cascade delete on itself?

 

[DomTrix]

I dont see a reason why it wouldnt delete records that refer to a deleted record's key (if that is what you mean, I aint hot on lingo), but i have never tried it on an inner join.

Ill do the maths tho - 1 million records = a meager c. 4MB space for the extra integer field

 

[DomTrix]

Just had a look at a temporary inner join and the option for cascading deletes is greyed out. Bummer. Just an extra line in any delete code should do it tho right?:

delete from communications
where communicationID = @aGivenCommID
or origcommID = @aGivenCommID

 

[megmogg]

OK, thanks for the help, deserves a vote.

Any one else have any different opinions/views?