Mulling it over now on the train I would say one table for the image types, one table for the users, one table for the user/image lookup, one for the event or alike.
So it would be tblImagetype --------- iImageTypeID int PK seed vcImageType nvarchar Or tinyint if you do not expect more than 255 types. tblImage --------- iImageID int PK seed ImageTypeID FK vcImageName nVarchar vcDescription nText iHeight int iWidth int tblUserImage ----------- iUserID int PK iEventID int PK iImageID int FK You could add the iImageTypeID to the tblUserImage table but in most cases you can get away without it. - I have assumed you already have an event table. N "This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions." Visit our website at http://www.reedexpo.com -----Original Message----- From: [EMAIL PROTECTED] <[EMAIL PROTECTED]> To: SQL <[email protected]> Sent: Tue Jul 05 04:30:44 2005 Subject: efficient design? I'm building out a new photo gallery and I'm looking at different DB designs. The gallery will have pictures on a per person basis as well as a per event basis. I was thinking the following table for the pics: imageid int primary userid int primary imagetype tinyint primary imagename nvarchar description text height int width int Note the imageid, userid and imagetype all being primary. I'm thinking that I can have the following example data: image 1 for userid 1 is a personal image (imagetype 1) image 2 for userid 1 is a personal image (imagetype 1) image 1 for userid 1 is an event image (imagetype 50 for cfunited) image 1 for userid 1 is an event image (imagetype 49 for powered by Detroit) image 2 for userid 1 is an event image (imagetype 49 for powered by Detroit) This would mean that I have an exact lookup for each user's images per type. Is this efficient? The normal way of doing this is to have a single imageid as an incremental int or by having a separate table for personal and event images. Your thoughts? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:6:2325 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/6 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
