Which design is better? - SQL Server Forums

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)

Which design is better?

Author

Topic

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-16 : 01:17:11

So I'm designing a simple photo gallery which users can upload pictures into and the user can select their "default" picture. Users can have multiple default pictures if they want to.

This table will possibly accumlate overtime and be very large.

Is it better to put a bit column in the IMAGE table or have a normalized table that connects users default images to images?

For an example:

First Design:
[images]
image_id_pk, user_id, , image_name, image_guid, is_user_default

SELECT * FROM images WHERE user_id = @userid AND is_user_default = 1 ORDER BY NEWID();

Second Design:
[images]
image_id_pk, user_id, , image_name, image_guid

[user_default_images]
user_default_id_pk, user_id, image_id
the image_id points to the image record in [images] table

SELECT * FROM images WHERE image_id IN (SELECT image_id FROM user_default_images WHERE user_id = @userid) ORDER BY NEWID();
I think second design is better because it will POSSIBLY contain less rows because not everyone upload pics will make the picture their default picture.

Which do you think will be faster? Any suggestions?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-16 : 07:33:55

The second design is not normalized at all -- it actually a bad design because it allows for users to have more than 1 default image, it doesn't have a strong primary key, and it contains redundant data (the imageID is already related to a userID) stored elsewhere.

If you want to store a user's default image, and if they can only have 1 default image, just add a DefaultImage column to the users table.

- Jeff
http://weblogs.sqlteam.com/JeffS

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-06-16 : 14:59:28

The first design is better, but why do you have a order by NEWID()? Use the column name

order by image_id_pk
or
order by image_GUID
etc..

dbwilson4
Yak Posting Veteran

50 Posts

Posted - 2007-06-16 : 21:56:55

Woops, I think you guys missed the part where I said "Users can have multiple default pictures if they want"

Therefore, these are valid "statements" that can be made about this app:
1) Users can have multiple DEFAULT pics
2) Users don't HAVE to have any pics
3) Users can have pics THAT are NOT default pics

Therefore, wouldn't the second table be better in a large app? For an example, if I query the table to get a users default pic (and because the user can have multiple default pics, I must sort by NEWID() - random order)...wouldn't the second design be faster because it contains LESS rows to seek/scan because not everyone will chose their pic to be a default pic etc.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-16 : 23:28:39

I did miss that. Then just add a "default" column to the images table. you should not be breaking it out into an unnecessary redundant table.

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources