What if multiple people are in an image? Your table structure will only let you associate that image w/ a single person (unless you have multiple entries for each image, which is bad). What happens when you have an image that isn't connected to a user? Or an image that isn't connected to an event. In your table structure, you'd be storing the "empty" data as UserID or ImageType.
I'd use your second option. Keep all the images in a single table, and have a separate table for EventImages and UserImages. something like this: User table (UserID, other stuff) ImageType (ImageTypeID, other stuff) Image Table (ImageID Primary, ImageName, Description, Height, Width) No composite key here And the intersection tables: UserImages (UserID primary, ImageID primary) EventImages (ImageTypeID primary, ImageID primary) This is the most flexible option. At 11:30 PM 7/4/2005, you wrote: >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:2320 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
