Discount codes table design

At my last company this is something similar to what we did, how you implement it is up to you! I suggest changing some things around, it's designed to suit multiple types of voucher code, multi-use vouchers etc... To key past your primary key constraint we used big integer primary keys.

Schema::create( 'vouchers', function ( Blueprint $table ) {
    $table->bigIncrements( 'id' );
    
    // The voucher code
    $table->string( 'code' )->nullable( );

    // The human readable voucher code name
    $table->string( 'name' );

    // The description of the voucher - Not necessary 
    $table->text( 'description' )->nullable( );

    // The number of uses currently
    $table->integer( 'uses' )->unsigned( )->nullable( );

    // The max uses this voucher has
    $table->integer( 'max_uses' )->unsigned()->nullable( );

    // How many times a user can use this voucher.
    $table->integer( 'max_uses_user' )->unsigned( )->nullable( );

    // The type can be: voucher, discount, sale. What ever you want.
    $table->tinyInteger( 'type' )->unsigned( );

    // The amount to discount by (in pennies) in this example.
    $table->integer( 'discount_amount' );

    // Whether or not the voucher is a percentage or a fixed price. 
    $table->boolean( 'is_fixed' )->default( true );
    
    // When the voucher begins
    $table->timestamp( 'starts_at' );

    // When the voucher ends
    $table->timestamp( 'expires_at' );

    // You know what this is...
    $table->timestamps( );

    // We like to horde data.
    $table->softDeletes( );
});

Schema::create( 'user_voucher', function ( Blueprint $table ) {
    $table->integer( 'user_id' )->unsigned( );
    $table->bigInteger( 'voucher_id' )->unsigned( );

    $table->unique( [ 'user_id', 'voucher_id' ] );
});

Schema::create( 'product_voucher', function ( Blueprint $table ) {
    $table->integer( 'product_id' )->unsigned( );
    $table->bigInteger( 'voucher_id' )->unsigned( );

    $table->unique( [ 'product_id', 'voucher_id' ] );
});

I hope this helps :)

@matberry yes, it is very helpful and intuitive, im really appreciated your suggestion, thanks :)

Really this great, so much clear flow. thanks

How will you check for max_uses_user if user_voucher table is unique by user_id and voucher_id ?

@mathewberry this is very helpful but i don't understood about "uses,max_uses,max_uses_user" so can you help on this, to explain how its work? please help me.

@hamedov that is a good point, this SQL format does not account for that scenario, however what I have done to combat this is; in my orders table I have a voucher_id, user_id and a small function on the Voucher model that checks if the user has the maximum usages when they next try to apply a voucher.

It looks something like this:

public function userReachedMaximumUses(User $user) 
{
    if ($this->max_uses_user <= 0) {
        return false;
    }

    return $this->orders()->where('user_id', $user->id)->count() < $this->max_uses_user;
}

This little function is only relevant to orders but you could also look into adding a used column on the user_voucher pivot which is a better approach for the long term.

@keyurdpatel The max_uses is the total number of times the voucher can be used, for example you may set max_uses to 6, then every time someone uses a voucher you will increment the uses column and check to before applying if uses is greater than or equal to the max_uses, if it is you don't apply.