You also want to consider adding a dUpdated, dCreated, and dDeleted columns to track creation, update and logical deletions of data.
-----Original Message----- From: Dina Hess [mailto:[EMAIL PROTECTED] Sent: 05 July 2005 13:11 To: SQL Subject: Re: efficient design? Michael, When I look at your proposed design, I see a different picture...no pun intended. :) tbl_event: event_id int not null identity PK event_name varchar(50) tbl_event: event_id event_name 5000 CFUnited 5001 Powered by Detroit ********** tbl_user: user_id int not null identity PK user_fname varchar(30) user_lname varchar(30) tbl_user user_id user_fname user_lname 50 Dina Hess 51 Michael Dinowitz ********** tbl_image: img_id int not null identity PK img_name varchar(50) img_caption varchar(100) img_height int img_width int img_type varchar(5) img_link varchar(100) user_id int FK event_id int FK feature_id FK (consider this) tbl_image: 100 DH_headshot lalalalalalalala 100 90 JPEG img/dh_headshot.jpeg 50 null 101 CFU_group_pic CFU group shot 200 300 GIF img/cfu_group.gif null 5000 ********** You also may want to add a foreign key to your image table to associate a featured section (FusionAuthority ezine) to an image. I added the link field to store the location of the image; knowing full well that others will tell you to store the whole image in your database and be done with it. :) Sure, you have a couple of joins to do to get at event and user data but I think this design will provide you with more flexibility. Dina ----- Original Message ----- From: "Michael Dinowitz" <[EMAIL PROTECTED]> To: "SQL" <[email protected]> Sent: Monday, July 04, 2005 10:30 PM 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:2321 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=11502.10531.6 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
