Buyer and Seller badge assignment module database design

Question

I am developing a B2B trading application, it has buyers and suppliers for various products.

I am trying to develop a "badge assignment module" that will be used to assign badges through admin panel to the buyers and suppliers according to their verification, buying/selling strength, +ve feedback and so on. A user can earn one or more badge.

The allotted badges should have an expiry date. Please help me in database design - what will be the required tables and the columns?

Please suggest if there is any open source/starter kit application in asp.net that is implementing the same logic.

Answer 1

For one, a buyer and seller should be in the same table, and you should use table inheritance to model them.

A buyer has many badge assignments.

A badge has many badge assignments too.

Therefore it is a many-many relationship.

Can a party get a badge, have it expire, then get the same badge again, later?

Your schema should look something like this:

create table party_type(
  id smallint primary key,
  description text not null
);

insert into party_type values (1, 'Buyer'), (2, 'Seller');    

create table party(
  id bigint identity primary key,
  type smallint not null references party_type (id),
  name text not null
);


create table badge(
  id bigint identity primary key,
  name text not null
);


create table party_badge(
  party_id bigint not null references party (id),
  badge_id bigint not null references badge (id),
  from_date datetime not null default current_timestamp,
  to_date datetime null default null,

  check (to_date > from_date),

  primary key (party_id, badge_id, from_date)
);