Advertisement Management System ERD (Simple Version)

Last Updated: November 10, 2023

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

 

ER DIAGRAM

First, here is the entire ER diagram and a quick overview of the system.

 

SIMPLE ADVERTISEMENTS SYSTEM

Attendance Management System ERD (click to enlarge)

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.

 

TABLE DETAILS

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

COLUMN DATA TYPE NOTES
customer_id INTEGER Primary key.
customer_name VARCHAR The customer’s name.
customer_email VARCHAR Customer’s email.
customer_tel VARCHAR Customer’s telephone.
customer_address VARCHAR Customer’s address.

As mentioned above, my assumption here is “corporate customers”. This table should contain a list of companies.

 

 

1B) CUSTOMER CONTACT TABLE

COLUMN DATA TYPE NOTES
contact_id INTEGER Primary key.
customer_id INTEGER Foreign key.
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

COLUMN DATA TYPE NOTES
ad_id INTEGER Primary Key.
customer_id INTEGER Foreign key.
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

COLUMN DATA TYPE NOTES
media_id INTEGER Primary Key.
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

COLUMN DATA TYPE NOTES
type_id INTEGER Primary Key.
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

COLUMN DATA TYPE NOTES
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

COLUMN DATA TYPE NOTES
category_id INTEGER Primary Key.
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

COLUMN DATA TYPE NOTES
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.

 

 

EXTRAS

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

 

THE END

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!