Redundant Columns in child tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Redundant Columns in child tables

Author

Topic

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-11 : 22:20:09

[album]
id
name

[album_image]
id
albumid

[album_comment]
id
albumid
imageid
userid

[album_comment_view]
id
albumid
imageid
commentid
userid <-- person who viewed

Couldn't I get ride of albumid from album_comment_view AND albumid from album_comment? I could instead just use IN clause to filter right? Would that cause performance loss if I query album_comment by ALBUMID?

For an example:
SELECT * FROM album_comment WHERE imageid EXISTS (SELECT imageid FROM album_images WHERE albumid = 1);

VS

SELECT * FROM album_comment WHERE albumid = 1;

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-12 : 00:23:13

Also, when I'm designing an album_image where it could either belong to a user, or a group BUT it must ATLEAST have a user, should i normalize the groupid OR leave it as allow null?

ex:
[album_image]
imageid
userid
groupid

VS

[group_images]
imageid
groupid

[album_images]
imageid
userid

Which would yield better performance? Which route should I go?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-14 : 05:26:49

Your asking questions about your data model which nobody here will truly be able to understand without some analysis. Do the model first then optimise later. Fill your database with data and use the profiler. Remember though - no matter how fast you make your code it's got to solve the problem first & foremost. There are actually rarely multiple ways of doing exactly the same thing. What I was going to suggest relys on assumptions so I won't (e.g. is the image ID on the comment the same as the album?). Basically if you duplicate stuff to avoid a table join then you've got it wrong.

On the subject of your images, this model seems odd, but if each images must have a user then I'd go a single image table, have an FK back to the mandatory user (or User has an imageID) and (judging from your suggestions) the group would need a nullable PK back to image ID or the image back to the group. You have not specified multiplicity requirements so it's hard to answer properly but again you seem to be considering performance over requirements which is clearly wrong.

JokerOfACoder
Starting Member

18 Posts

Posted - 2007-05-14 : 12:39:03

Users can have multiple albums, albums can have multiple images, images can have multiple comments. My column naming convention might be confusing you...userid on image table is actually an FK on WHO owns the image (which is going to be the same as the image's album).

Albums need a "userid" to show who the album belongs to. Images need an "albumid" to show where the image belongs to.

However, Should images ALSO have a userid to show who the image belongs to OR should I just figure it out by joining the images with the album? If go the latter route, won't it affect performance?

For an example:
SELECT * FROM images WHERE userid = 1;

VS

SELECT * FROM images WHERE albumid EXISTS (SELECT albumid FROM albums WHERE userid = 1);

SELECT * FROM images_comments WHERE albumid = 1;

VS

SELECT * FROM images_comments WHERE imageid EXISTS IN (SELECT imageid FROM images WHERE albumid = 1);

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-14 : 16:30:25

If I am reading this correctly, then yes you would have a UserID on Image. How else do you know who ownes it? How can you figure out who own the image by joining to album if the image is in more than one album?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources