Issue in Developing Conceptual Model for My Website

Question

I want to discuss a non-code related issue. Rather it's an issue with the concept or data model itself.

I am building a business directory website using Laravel.

I have made a packages table, implemented packages CRUD functionality for the back-end and implemented a packages list, with sign-up buttons for the front-end. When a sign-up button is pressed, the package ID is passed to the sign-up route.

I have added package_id as a foreign key to the users table. But the issue I have now, is that if a user goes directly to the sign-up route, there is no package ID. I therefore would like to sign the user up for a free package.

I can't use a LIKE query because of dynamic packages and can be changed the name and id as well if free package is deleted and again added and how to handle exception if free package was deleted.

Please help me with better methodology.

Answer 1

When you post user data from sign up route then check if the package ID exists then fine use that otherwise get package ID of your free package and then use that to sign them up with package ID.

This is like saying user cannot sign up without a package ID but where do I get package ID when they come direct on sign up. When you post back you will not get package ID or get null or 0 whatever you have in postback or ajax sign up form. Use that to check if its a valid package ID or not then if its not then obtain ID of your free package (you can use any sql to obtain it from packages table only and once you have ID of free package then start signing up user and insert that ID into database and it wont complain). This way you are covered for both scenarios where no package ID is passed or someone try to send a wrong package ID.

Answer 2

As you said using a text search to match records is a bad practise, there are lot of ways to correctly address the problem:

1. Add a boolean field is_free; if you want to make it unique make it nullable, unique, and default null but remember the value should be null for non-free or true for the only free package or the unique constraint will fail (the field supports multiple null items but only one true or false)
2. Get the first package where the price equals to zero
3. Make a foreign key to package_groups and create a "free" group, a bit over-complicated if you do not need to separate packages into groups

Let's assume your package sign-up route is

/sign-up/{package_id?}

note the "?" after package_id, it tells the parameter may be missing.

The logic if i understood it correctly is :

• if the package_id is specified sign-in for the package with that id
• if the package_id is null register for a free package

The route should be like this:

Route::get('/sing-up/{package_id?}', 'PackageController@signUp');

And the controller should be doing something like this:

PackageController extends Controller{
//....
    public signUp($id=null){
       $package=($id==null)?Package::free()->first():Package::findOrFail();
       //If $package is null there are currently no active free packages
       ///sign-up logic here...
    }

}

Assuming Package::free() is a scope that filters the free packages, an example:

Package extends model{
//....
    function scopeFree($query){
        return $query->where('is_free',true); //for option 1. 
    }
}