Table Design: Using parents

Databases
1

Let’s say I have a form with a drop-down called ‘options’

I have different forms. The list of options vary, but sometimes the same option is needed on different forms.

There are about 10 forms in all, and each one has some different and some of the same options.

Example Form 1
Options:
Working
Attempted Call
Emailed Quote

Example Form 2
Working
Emailed Quote
Not Interested
Submitted

I figured I could create a table like this:


id          parent        option
--------------------------------
1          NULL          form1
2          NULL          form2
3          1             Working
4          1             Attempted Call
5          1             Emailed Quote
6          2             Working
7          2             Emailed Quote
8          2             Not Interested
9          2             Submitted

This might look pretty ridiculous, but at least I stopped myself and came here before going on! I’d rather not be duplicating data here, but I don’t see how else to design the table. Any suggestions?

2

  1. I’m not sure why you want to store the options in the database, why not just hard code them in the HTML? Or are they subject to a lot of change so it’s easier to manage them dynamically.

  2. If you really want to put them in the database I would do it like this:


TABLE: options

id         option
--------------------------------
1          Working
2          Attempted Call
3          Emailed Quote
4          Not Interested
5          Submitted

TABLE: forms

form_id    option_id      position
----------------------------------
1          1              1
1          2              2
1          3              3
2          1              1
2          3              2
2          4              3
2          5              4

Mainly because that doesn’t repeat option names so if you ever have to change one you only need to change it once.
Also I feel that forms and options are completely different entities and mixing them in the one table just looks weird / feels wrong.

The position column is meant to order the options by, but is completely optional; you can also order by some other criteria if you like.

3

My client likes to make little phpMyAdmin changes to the database where he can, so I said I’d try to build an options table for him. I built this in a PHP reference file already, but he wanted to update the forms himself.

You’ve given me some good ideas here. Thanks for the assistance, once again.

4

You’re welcome. And whatever you do, don’t be tricked into using ENUM. Ask @r937; if you want to know why not :smiley: