Query explaination

Question

I recently started learning SQL and wanted to see if someone could give me a quick overview of this query and how tell me if my implementation would be correct. The part where I lack the knowledge is the "check" statement Query is as follows:

ALTER TABLE
  partner_pricing
ADD
  partner_description VARCHAR(8) NOT NULL CHECK (partner_description IN ('Direct', 'Reseller'));
----------------------------------------------------
// Html

<form>
  <select>
    <option value="Reseller">Reseller</option>
    <option value="Direct">Direct</option>
  </select>
 </form>

Many thanks.

Answer 1

You question is kind a broad but I would tell you the considerations I made.

First you need a table for your options lest call it partner_description

partner_description_id (pk) description
         1                    Reseller
         2                    Direct

Having a separated table allow you made changes to the "options" without affect your application. If you want show the options on your page you do:

  SELECT * FROM partner_description

The HTML should looks like

<select>
  <option value="1">Reseller</option>
  <option value="2">Direct</option>
</select>

In your table partner_pricing you will have partner_description_id with FK constraint. That will validate you won’t insert any wrong value. Also, because you save the id instead of the string you save fewer data and if you change the description don't have to change the all data.

Answer 2

Ah ok, appreciate that information. Any recommendation to how i could modify my query If i want a column in my table to only have the options of reseller / direct user?

Use enums

ALTER TABLE
  partner_pricing
ADD
  partner_description ENUM ('Direct','Reseller')