CODEWITHSUNDEEP
phpdatabaselaravel
Mick
Mick

Reputation: 1561

How to store lists of options in a web application

We are currently designing a new database and I'm not sure how I want to store my lists of options.

For example, if you have a list of priorities:

1  High
2  Medium
3  Low

or type:

1 email
2 phone
3 text

How should I be storing these?

Historically I have placed them in a database table ( Option 1):

Priorities

id description
1  High
2  Medium
3  Low

The result of this, is that they become foreign keys in other tables.

An alternative (Option 2), is to store them in code, as a list of constants.

Pros and Cons

Option 1

Option 2

Have I missed anything? What is common practise?

Cheers

Upvotes: 1

Views: 215

Answers (3)

Mihir Bhende
Mihir Bhende

Reputation: 9055

I would decide based on following factors :

1. Number of available values :

If it's just a set of 2 or 3 predefined values like priorities High, Medium, Low.. I would prefer to have it in codebase as constants. Because it is highly unlikely any new option will be added like very low or very very low etc

If the values are predefined but large in numbers, like maybe skillsets of an employee.. I would definitely put it in a database table.

2 . Possibility of values changing :

If these values are changing frequently and you want to give control to user maybe via an admin panel to manage these values, definitely store those in database table.

3. The usage frequency :

If these values are core of your system so that it's used in almost all queries to filter the results, then you need to also account for the performance :

For example : Let's say entire project depends upon High, Medium, Low priorities. You need to filter almost every query using the priorities then :

Having Constants will have the priority stored in tasks table :

$tasks = Task::whereIn('priority', [high_constant, low_constant])->get();

Having a separate table priorities and having relationship will with tasks using priority_id foreign key have:

$task = Task::whereHas('priority', function($q){
    return $q->whereIn('priority_name', ['high', 'low']);
})->get();

Depending on number of data you have and frequent queries you do, you can check which is affecting performance. If the difference is very small, I would prefer constants with points 1 and 2 taken care of.

4. Extras like Getting the ID :

Let's say you have a page to create new task which has a dropdown of priorities.

If you have a separate table, you will have to get the ID of that priority from the priorities table to save it as a foreign key while saving task in tasks table. (Really not a huge deal though but good to mentioning)

If you have constant, you can save that value directly.

Each of it having pros and cons, I would make my choice mostly in point 1 and 2 above.

Also, if you are making constants, I would suggest to make a laravel facade with a n alias and then have constants defined in it. So you can use it something like through out your project

Priority::HIGH or Priority::LOW

Upvotes: 4

Peter Szalay
Peter Szalay

Reputation: 386

I would go with option 2:

You can put it in an array in a seperate file, outside your code, so it would be easier to manage, like:

types.php

<?php
return ['type' => ['email','phone','text']];

And require it where you need it:

require '/types.php';

Store the array in a variable like $options and use $options['type'] in your code.

Ofcourse you would need to push your code again. Other option would be caching it.

Upvotes: 0

mrQubeMaster
mrQubeMaster

Reputation: 342

I usually do it with the database and foreign keys. You are already hitting the database for the information, with a simple you you can grab the right value's instead of the id's.

But if its just these 2 lists... I'm not sure, i try to not use hardcoded value's
in my code to make easy changes without updating the production server.

Upvotes: 0

Related Questions