Leigh - One table is a list of editorials that appear on the website. The other table is a list of advertisers.
When someone views an editorial, we want advertisers that are relevant to that editorial to show on that web page.
To accomplish that, I made a system where my client can decide which ads get related to which editorials. The advertiser names get thrown into a column of the Editorials table. So, for each record in the Editorials table, there is a column showing which ads relate to that editorial, separated by commas.
I know there's a better way, but that's all I could come up with.
That is a many-to-many relationship. ie One editorial can be related to many ads and vice versa. It's best represented with three tables. Such as:
Table: [Editorial] Columns: [ EditorialID (PK), EditorialText, ... ]
Table: [Advertiser] Columns: [ AdvertiserID (PK), AdvertiserName ]
Table: [Editorial_Advertisers] Columns: [EditorialID, AdvertiserID]
The advertiser names get thrown into a column... my client can decide which ads get related to which editorials |
The third table stores the relationships between editorials and ads. So instead of storing a list of advertisers, you'd insert one record for each EditorialID + AdvertiserID combination. Then you could use JOINs to retrieve all the ads for EditorialID 123.
SELECT a.AdvertiserName
FROM Editorial_Advertiser ea INNER JOIN Advertiser a ON ea.AdvertiserID = a.AdvertiserID
WHERE ea.EditorialID = 123
AdChoices