Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Aug 2003
    Location
    West
    Posts
    101
    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.

  3. #3
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311

    Re: relationship question

    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
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •