1

I have several list boxes in my web application that user has to fill. Administrator can add/remove/edit values in the combo box from controle panel. so problem is what is the best way to keep these combo box in database.

one way is keeping each table for each combo box. I think this is very easy to handle but I will have to create more than 20 tables for each combo/list box.And I think whether it is good practice to do so.

anotherway is keeping one table for all combo box. But I am worring when deleting data in this case. If I want to remove India from countr coloum in combo box table, then I will be in problem. I may have to update it to null or some otherway and have to handel this in programming side.

Am I correct. can you help me ?

laurent
  • 88,262
  • 77
  • 290
  • 428
kiriappa
  • 822
  • 2
  • 7
  • 14

4 Answers4

2

I think you just should create a table with 3 fields. First field is the id, second is the name and the last is the foreign key. For example:

combo_box_table
id - name - box
1 - Japan - 1
2 - India - 1
3 - Scotland - 2
4 - England - 3

you just have to play with query, each box represent the last field. 1 represent combo box 1 and 2 represent combo box 2 etc. select * from combo_box_table where box = 1

if you want to delete india the query is just delete from combo_box_table where id = 2

May this help

Harmando Taufik Gemilang
  • 96
  • 6
  • if box is foriegn key where is refering table? – kiriappa Dec 20 '12 at 04:22
  • no need referring table. if you want to display the menu in combo box 1, you play with query i have provided above. – Harmando Taufik Gemilang Dec 20 '12 at 04:30
  • yes this is good, Only problem comes to my mind if they ask any relationship between list box like when they select a depart ment showing only available job possions in the department list box. I will ask this from costomer. It is totaly depended on him now. :) thanks – kiriappa Dec 20 '12 at 04:47
  • I think still that way we can handle this but I have to keep flags for this and use several coloums but it will make this complex. – kiriappa Dec 20 '12 at 04:53
  • what do you mean by that? Could you please explain me further? I don't really get what you mean. – Harmando Taufik Gemilang Dec 20 '12 at 05:04
  • If you go to intel web site, they will show you lot of drivers for machines. but when you select your motherboard then they will show list of OS to choose. like that when there is a relatioin amoung list data. – kiriappa Dec 20 '12 at 05:42
  • sorry above two comments should go to @Branko Dimitrijevic – kiriappa Dec 20 '12 at 06:43
  • I see, this is possible to do like in http://downloadcenter.intel.com/ all you have to do is just add more fields and that fields related the previous fields – Harmando Taufik Gemilang Dec 20 '12 at 06:50
  • @kiriappa Added an answer showing how I would handle the above relation issue. – SnareChops Dec 20 '12 at 14:56
1

Another possibility would be to save the combo box data as an array or a json string in a single field in your table, but whether you want to do this or not depends on how you want your table to function and what you application is. See Save PHP array to MySQL? for further information.

EDIT:

I'm going to assume you have a combo-box with different countries and possibly another with job titles and others.

  1. If you create multiple tables then yes you would have to use multiple SQL querys, but the amount of data in the table would be flexible and deleting would be a one step process:

    mysqli_query($link,"DELETE FROM Countries WHERE Name='India'");

  2. With the json or array option you could have one table, and one column would be each combo-box. This would mean you only have to query the table once to populate the combo-boxes, but then you would have to decode the json strings and iterate through them also checking for null values for instance if countries had 50 entries but job titles only had 20. There would be some limitations on data amount as the "text" type only has a finite amount of length. (Possible, but a nightmare of code to manage)

You may have to query multiple times to populate the boxes, but I feel that the first method would be the most organized and flexible, unless I have mis-interpreted your database structure needs...

A third possible answer, though very different, could be to use AJAX to populate the combo-boxes from separate .txt files on the server, though editing them and removing or adding options to them through any way other than manually opening the file and typing in it or deleting it would be complex as well.

Community
  • 1
  • 1
SnareChops
  • 13,175
  • 9
  • 69
  • 91
  • my application is a web based HRM system. So there is lot of list box to fill. That's there is lot of listbox to configure from controle pannel. Only admin and other super users only can go to this web. I think now it is clear. – kiriappa Dec 20 '12 at 03:57
  • 1
    @kiriappa I think in this case creating a single table for each box is what I would do. – SnareChops Dec 20 '12 at 04:01
  • can you explain why do you think so? – kiriappa Dec 20 '12 at 04:02
  • 1
    @user1876470 (Sorry can't comment under their post (not enough rep)) That could be another way. Basically it would be id=autoIncrement (only for database cleanliness) name='The entry for any of the boxes) and box='Which box it should appear in' so if you had 1/India/1 , 2/Japan/1 , 3/Engineer/2 , 4/Painter/2 , 5/China/1 Then you could use `"SELECT Name,Box FROM ComboBoxData"` and then use a `switch()` to distribute the values as appropriate. – SnareChops Dec 20 '12 at 04:34
  • I use this solution that is using seperate table since I can query data easily and and future changes in database schema. thanks guys – kiriappa Dec 22 '12 at 05:39
  • @kiriappa Glad we could help. – SnareChops Dec 22 '12 at 05:54
1

Unless you have some extra information at the level of the combo-box itself, just a simple table of combo-box items would be enough:

CREATE TABLE COMBO_BOX_ITEM (
    COMBO_BOX_ID INT,
    VALUE VARCHAR(255),
    PRIMARY KEY (COMBO_BOX_ID, VALUE)
)

To get items of a given combo-box:

SELECT VALUE FROM COMBO_BOX_ITEM WHERE COMBO_BOX_ID = <whatever>

The nice thing about this query is that it can be satisfied by a simple range scan on the primary index. In fact, assuming the query optimizer of your DBMS is clever enough, the table heap is not touched at all, and you can eliminate this "unnecessary" heap by clustering the table (if your DBMS supports clustering). Physically, you'd end-up with just a single B-Tree representing the whole table.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
0

Use a single table Countries and another for Job Descriptions setup like so:

Countries

ID | Name  | JobsOffered | Jobs Available
_________________________________________
1  | India | 1,2,7,6,5   | 5,6
2  | China | 2,7,5,      | 2,7
etc.

Job Descriptions

ID |       Name       | Description
___________________________________
1  | Shoe Maker       | Makes shoes
2  | Computer Analyst | Analyzes computers
3  | Hotdog Cook      | Cooks hotdogs well

Then you could query your database for the country and get the jobs that are available (and offered) then simply query the Job Description table for the names and display to the user which jobs are available. Then when one job is filled or is opened all you have to do is Update the contry table with the new jobID.

Does this help? (In this case you will need a separate table for each combo-box, as suggested, and you have referencing IDs for the jobs available)

SnareChops
  • 13,175
  • 9
  • 69
  • 91