View Single Post
Old 10-10-2012, 07:42 AM   PM User | #10
Old Pedant
Supreme Master coder!

 
Old Pedant's Avatar
 
Join Date: Feb 2009
Posts: 23,162
Thanks: 59
Thanked 3,992 Times in 3,961 Posts
Old Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to allOld Pedant is a name known to all
As I see it you need these tables:
Code:
CREATE TABLE Categories (
    catid INT AUTO_INCREMENT PRIMARY KEY,
    catname VARCHAR(100)
);

CREATE TABLE Countries (
    coid INT AUTO_INCREMENT PRIMARY KEY,
    coname VARCHAR(100)
);

CREATE TABLE Customers (
    custid INT AUTO_INCREMENT PRIMARY KEY,
    coid INT FOREIGN KEY REFERENCES countries(coid),
    custname VARCHAR(100))
);

CREATE TABLE Offers (
    offerid INT AUTO_INCREMENT PRIMARY KEY
    catid INT FOREIGN KEY REFERENCES categories(catid),
    offerdetails .... one or more fields to describe the offer ...
);

CREATE TABLE OffersByCountry (
    offerid INT FOREIGN KEY REFERENCES offers(offerid),
    coid INT FOREIGN KEY REFERENCES countries(coid)
);

CREATE TABLE CompletedOffers (
    custid INT FOREIGN KEY REFERENCES custormers(custid),
    offerid INT FOREIGN KEY REFERENCES offers(offerid)
);
The foreign key reference in magenta there may not be necessary, but it is likely a good idea.

The syntax there for the foreign keys isn't quite correct for MySQL, but it's a compact form that easy to read for a post like this.
__________________
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Old Pedant is offline   Reply With Quote