Need Help Normalising a Database!

[Tehuti]

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

[edited by: Tehuti at 2:58 am (utc) on Sep. 2, 2008]

[ZydoSEO]

I think you're on the right track. I would create a lookup table name something similar to 'Coupon_Instruction_Lookup' like the following:

Coupon_Instruction_Lookup:

ID
Description (optional)
DisplayText

With rows containing:

1,'Automatic','Automatic coupon. Click to activate.'
2,'Not Required','Coupon not required. Click to visit sale.'

Then I would replace the 'Coupon_Instruction' field in Coupon_Table with an INTEGER field called Coupon_Instruction_Lookup with a foreign key reference to Coupon_Instruction_Lookup.ID.

[edited by: ZydoSEO at 12:51 pm (utc) on Sep. 2, 2008]

[Tehuti]

Thank you for the suggestion, ZydoSEO!

However, it seems that I wasn't on the right track: that database was configured completely wrong. I think I've now got it right:

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?

Basically, I didn't need things like advertiser_name and advertiser_logo in Coupon_Table. All I needed was advertiser_id, which would reference the advertiser_name and advertiser_logo.

[piatkow]

At a quick glance that looks like third normal form to me.

[Tehuti]

OK. I'm trying to create the database. The problem is, I get a strange error when I try to create my last table (Coupon_Table). It's the only one with foreign keys, which I think are causing the error. What am I doing wrong? Please advise, guys.

Database

Advertiser_Table

advertiser_id
advertiser_name
advertiser_logo
advertiser_url

-------------

Offer_Instructions_Table

offer_instructions_id
offer_instructions
block_anchor_text

-------------

Category_Table

category_id
category_name
category_url

-------------

Coupon_Table

coupon_id
offer_anchor_text
offer_url
coupon_code
offer_expiration_date
advertiser_id
offer_instructions_id
category_id

Code

create table Advertiser_Table (
advertiser_id smallint unsigned not null auto_increment primary key,
advertiser_name char(25),
advertiser_logo char(25),
advertiser_url char(30)
) engine=innodb;

create table Offer_Instructions_Table (
offer_instructions_id smallint unsigned not null auto_increment primary key,
offer_instructions char(45),
block_anchor_text char(10)
) engine=innodb;

create table Category_Table (
category_id smallint unsigned not null auto_increment primary key,
category_name char(20),
category_url char(36)
) engine=innodb;

create table Coupon_Table (
coupon_id smallint unsigned not null auto_increment primary key,
offer_anchor_text char(40),
offer_url varchar(200),
coupon_code char(25),
offer_expiration_date date,
foreign key (advertiser_id) references Advertiser_Table (advertiser_id),
foreign key (offer_instructions_id) references Offer_Instructions_Table (offer_instructions_id),
foreign key (category_id) references Category_Table (category_id)
) engine=innodb;

ERROR 1072 (42000): Key column 'advertiser_id' doesn't exist in table

[ZydoSEO]

You haven't defined advertiser_id, offer_instructions_id, and category_id in the Coupon_Table at the point you reference it in the FOREIGN KEY statement. Try:

create table Coupon_Table (
coupon_id smallint unsigned not null auto_increment primary key,
offer_anchor_text char(40),
offer_url varchar(200),
coupon_code char(25),
offer_expiration_date date,
advertiser_id smallint unsigned,
offer_instructions_id smallint unsigned,
category_id smallint unsigned,
foreign key (advertiser_id) references Advertiser_Table (advertiser_id),
foreign key (offer_instructions_id) references Offer_Instructions_Table (offer_instructions_id),
foreign key (category_id) references Category_Table (category_id)
) engine=innodb;

[Tehuti]

Thank you very much, ZydoSEO! I'll try it right now.

But one quick thing . . .

How does my database creation code (or whatever it's called) look to you? Have I missed anything? For example, have I missed a NOT NULL anywhere, or anything else?

[ZydoSEO]

That depends on your application. But my guess is you're missing some. For example, would it make sense to you to create an Advertiser with no name, no logo, and no URL? The way your table is defined, I can do so using something like:

INSERT INTO Advertiser_Table (advertiser_name) VALUES (NULL)

This would give me a new row in Advertiser_Table but the only data in the row would be an Advertiser_ID generated via auto increment. Does that seem like something you want? Typically there is some minimum amount of data you expect every row in a table to have (other than the AUTO_INCREMENT primary key field). Those are typically set to NOT NULL on table creation to enforce that they are required as a minimum to create a row in the table. Perhaps you feel you should ALWAYS have an advertiser_name value for EVERY advertiser_table row. If so then make advertiser_name NOT NULL. It depends on your app and what you think should ALWAYS be there.

PS: If it were me, I would lose the _TABLE off of the end of the table names. But that's just me. If you end up doing a lot of work on this database over the years, you'll get tired of typing that every time. You'll be like, "I know it's a table." hehe

We have naming standards to do something similar. We don't use underscores. We used mixed case instead. Instead of _TABLE, _VIEW, _UPDATE_TRIGGER, _INSERT_TRIGGER, etc. on the end of database object names, we prepend the name with a lowercase letter or two to represent the same thing. We use the following lowercase prefixes:

t = Table
v = View
tu = Update Trigger
ti = Insert Trigger
fk = Named Foreign Key constraint
pk = Named Primary Key constraint
etc...

For example, if I were modeling your DB I would have named your tables:

tAdvertiser
tOfferInstruction
tCategory
tCoupon

But we are a Microsoft shop using SQL*Server which is not case sensitive. Not sure if that works in MySQL in a UN*X environment. I'm guessing the DB is case insensitive with the SQL you right.

Again, it's all personal preference. No real right or wrong way to name a table. But it helps if you have naming standards for that kind of stuff. The important thing is to stay consistent.

Good luck.

[edited by: ZydoSEO at 7:11 pm (utc) on Sep. 3, 2008]

[Tehuti]

Thank you, ZydoSEO, for a very informative response!

If I may, I have another quick question to anyone that might respond . . .

I have created my databse. The problem is, I can't seem to select data from two tables. When I do, I get a lot of duplicate data. For example, the command SELECT coupon_id, advertiser_name FROM Coupon_Table, Advertiser_Table; returns:

1 Amazon.com
2 Amazon.com
3 Amazon.com
1 Buy.com
2 Buy.com
3 Buy.com
1 CarCovers.com
2 CarCovers.com
3 CarCovers.com

The same is returned with SELECT Coupon_Table.coupon_id, Advertiser_Table.advertiser_name from Coupon_Table, Advertiser_Table;.

What could the problem be? Why is the same advertiser_name repeating for each coupon_id?

[LifeinAsia]

Try:
SELECT Coupon_Table.coupon_id, Advertiser_Table.advertiser_name
FROM Coupon_Table, Advertiser_Table
WHERE Coupon_Table.advertiser_id=Advertiser_Table.advertiser_id

[Tehuti]

Thank you, LifeinAsia! It worked!

May I ask you guys another question, please?

Is the code rendered in bold below the right way to get a second variable to show in place of a first variable if the first one is NULL?

It seems to work, but I'm sure it's incorrect. Basically, I want coupon_code to show unless it is NULL, in which case I want offer_instructions to show.

--------------------------

<?php

$con = mysql_connect("localhost", "root");

if (!$con)

{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("ctyi", $con);

$result = mysql_query("SELECT Coupon_Table.offer_anchor_text, Coupon_Table.offer_url, Coupon_Table.coupon_code,
Offer_Instructions_Table.offer_instructions FROM Coupon_Table, Offer_Instructions_Table WHERE Coupon_Table.offer_instructions_id=Offer_Instructions_Table.offer_instructions_id");

while($row = mysql_fetch_array($result))

{
echo "<table border=\"0\">";
echo "<tr>";
echo "<td><a href=\"" . $row['offer_url'] . "\">" . $row['offer_anchor_text'] . "</a><br />"
. $row['coupon_code'] . $row['offer_instructions'] . "</td>";
echo "</tr>";
echo "</table>";
}

mysql_close($con);

?>

[Tehuti]

I no longer need help with the above question, guys. Thanks for everything.