Below is my first ever attempt at a database. I'm trying to normalise it, but I've hit a complication. The database has a one-to-many (1:N) configuration. The problem regards the coupon_instructions column (rendered in bold) of the Coupon_Table table. The information this column will contain will either be a unique coupon code or one of these two exact sentences: Automatic coupon. Click to activate Coupon not required. Click to visit sale Since these two sentences will be repeated many times throughout the database, I thought that I should put them in their own table, which I called Coupon_Instructions_Table. Was that the correct thing to do? I'm also a little confused as to how I must enter data into my database. When filling in, say, the advertiser_logo information of Coupon_Table, do I just enter the advertiser_id from the Advertiser_Table table? Any help will be appreciated. ------------- Coupon_Table coupon_id advertiser_logo offer_anchor_text offer_url coupon_instructions expiration_date advertiser_name advertiser_url category_name category_url block_anchor_text ------------- Advertiser_Table advertiser_id advertiser_logo advertiser_name advertiser_url ------------- Category_Table category_id category_name category_url ------------- Coupon_Instructions_Table coupon_instructions_id coupon_instructions ------------- Block_Anchor_Text_Table block_anchor_text_id block_anchor_text
Yes. In the Coupon_Table table, you'll have the field coupon_instructions_id, which you'll link to the same field in the Coupon_Instructions_Table table when you want to get the text. Yes. In the Coupon_Table table, you'll have the field advertiser_id. To get the logo, you'll just link to the Advertiser_Table with this field.
As CreativeClans said - which also means you don't need the advertiser_logo, name or url columns in your Coupons table. Just have advertiser_id, then link to the advertiser table and pick up the information that way. Unless of course, your advertisers can have multiple campaigns each with a with different URL, logo etc, in which case you would need to cater for that with a Campaign Table etc...the final Database design depends on your current (and potential) requirements.
Thanks, guys, for your responses! However, I must say that you've confused me . . . Below is a revised, simplified version of my database. Coupon_Table is the main table that I will be adding to every day. For each coupon that I add to the table, I will need to include a store_name, store_logo and store_url. Since many coupons can belong to a single store, I have put these things in their own table, called Store_Table. Now, when filling in the store_name, store_logo and store_url columns of Coupon_Table, do I just enter the store_id from Store_Table? If so, why must the coupon_instructions_id column of Coupon_Table be called such instead of just coupon_instructions? How would you guys structure my database and name it's columns? Any help will be very, very much appreciated. Coupon_Table coupon_id Coupon_anchor_text Coupon_url coupon_expiration_date coupon_instructions_id store_name store_logo store_url category_name category_url ------------- Store_Table store_id store_name store_logo store_url ------------- Category_Table category_id category_name category_url ------------- Coupon_Instructions_Table coupon_instructions_id coupon_instructions
Actually, guys, I think I get it now . . . According to what you've both said, the following should be my database in 3NF: Coupon_Table coupon_id offer_anchor_text offer_url offer_expiration_date offer_instructions_id advertiser_id category_id ------------- Offer_Instructions_Table offer_instructions_id offer_instructions block_anchor_text ------------- Advertiser_Table advertiser_id advertiser_name advertiser_logo advertiser_url ------------- Category_Table category_id category_name category_url Is that correct? Whatever the case, I have added points to both of your reputations. Thank you for the help!