relationship question

I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.Well, if you create a table with "ad_ID then a number like 46_1.jpg, 46_2.jpg" ... then your select statement is very simple. "Select * from ad_table where ad_ID = ??" and your insert statement is simple. However, if you create a table with ad_id, ad1, ad2, ad3, ad4, ad5, ad6, ad7, ad8 .... then you are limited to 8 pictures for that ad ... if you some day need ad9 you will have to alter the table. Either way works but one is more scalable.|||Originally posted by lisa1958
I am having a debate with someone here at work on how a database should be laid out. We have a table of ads for boats. An ad can have 0 to 8 pictures. Do you create 8 columns in the ad recordset for the possible 8 photos for that ad or do you create a separate table that has the ad_ID and a new row for whatever pictures are associated with it. Pictures can be added and deleted at anytime. Currently we are naming the photos with the ad_ID then a number like 46_1.jpg, 46_2.jpg. We are only storing the file name, not the data in the database. We are using SQL not Access.

Well, using the Normal Forms (i thought the third one), the answer should be : store the possible pictures of an ad in a seperate table. Since you don't know how many pictures there'll be for an ad, you don't want to create columns for. Just think of the possibility that in the future more than 8 pictures must be stored for an ad, and u only have 8 columns for it in the ad table. In that case you should change the datamodel and all kinds of interfaces. Brrrrrrrrr
So, create a table for the ad, a table for the ad-pictures and a foreign key between them

Hope this helps