Welcome to a quick sharing of an ERD of an advertisement management system. But before we start, there are quite a few different “types of advertisements” these days – Digital, billboard, video, flyers, brochures, etc… So just a quick disclaimer, we are not just talking about “online advertisements” here. Read on for the example!
TABLE OF CONTENTS
First, here is the entire ER diagram and a quick overview of the system.
SIMPLE ADVERTISEMENTS SYSTEM
To keep this system simple, I have generalized the entities into 4 groups.
- Advertisements – To address the elephant in the room, the advertisements or campaigns.
- Customers – To address the other elephant, the clients.
- Types – As in the introduction, “online” is not the only mode of advertisement. This can be “billboard”, “poster”, “online”, “flyers”, or whatever else.
- Category – This is different from “types”. For example – Food, education, motor, insurance, etc…
FOR REFERENCE ONLY
Before the “expert trolls” scream “the diagram is not accurate” and “this is useless” – This is only an example for your reference, and some assumptions were made.
- A customer can have none or many contact persons – Tricky. A customer can be a company or an individual. In the case of an individual, the customer is the contact person. So yep, you decide.
- A customer can have no running advertisement contracts. Once again, if you enforce “must be customers” and not “potential customers” – Feel free to change the relationship to one/one-many.
- Categories are kind of optional, go ahead and remove them if you don’t need them.
Yes, this list can go on forever. Since you are building your own system now, you have to decide your own rules.
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) CUSTOMERS
1A) CUSTOMERS TABLE
|customer_name||VARCHAR||The customer’s name.|
As mentioned above, my assumption here is “corporate customers”. This table should contain a list of companies.
1B) CUSTOMER CONTACT TABLE
|contact_name||VARCHAR||Contact person’s name.|
|contact_email||VARCHAR||Contact person’s email.|
|contact_tel||VARCHAR||Contact person’s telephone.|
A company client can have multiple contact persons. Use this table to record all of them. But if the client is an individual (not a company), there are a few ways to deal with it:
- Leave the individual’s information in the customer table, there are no contact persons.
- Duplicate the individual’s information into this table as well.
- Add a “customer type” flag to the customer table – Corporate, individual, education, government, agency, etc…
Well, you decide which works the best.
PART 2) ADVERTISEMENTS
2A) “MAIN” ADVERTISEMENTS TABLE
|ad_name||VARCHAR||Name of the ad campaign.|
|ad_description||TEXT||A description of the ad campaign.|
|ad_start||DATETIME||Campaign start date.|
|ad_end||DATETIME||Campaign end date.|
This table should be pretty self-explanatory. Feel free to add more fields to this table as required – Maybe an “ad details” field to contain whatever grandmother story the client wants.
2B) AD MEDIA TABLE
|media_name||VARCHAR||Name of the media.|
|media_description||VARCHAR||Description of the media.|
|media_file||VARCHAR||The actual file location.|
I have 2 lines of thought here:
- The client provides the ad creatives, you run the campaign. This table is used to store those media.
- You create whatever copywriting, images, photos, videos, audio for the ad – Store it in this table for tracking.
Well, feel free to remove this table if it provides no value.
PART 3) ADVERTISEMENT TYPES
3A) AD TYPES TABLE
|type_name||VARCHAR||Name of the ad type.|
|type_description||VARCHAR||Description of the ad type.|
As in the introduction, this should contain entries like billboards, bus ads, online, cinema ads, etc…
3B) AD TO TYPES TABLE
|ad_id||INTEGER||Primary and foreign key.|
|type_id||VARCHAR||Primary and foreign key.|
Ties ads to their respective types.
PART 4) ADVERTISEMENT CATEGORIES
4A) AD CATEGORIES TABLE
|category_name||VARCHAR||Name of the category.|
|category_description||VARCHAR||Description of the category.|
Once again, this is different from “ad types”. This should contain entries like food, education, services, etc…
4B) AD TO CATEGORIES TABLE
|ad_id||INTEGER||Primary and foreign key.|
|category_id||INTEGER||Primary and foreign key.|
Finally, a table to hold payment details and history. Well, customers can pay half by cash, and half by credit card. This table will keep the options open.
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
- Classiphpied PHP MYSQL Classified Ads System – Code Boxx
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!