The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

More efficient updates (DB)

I'd like to ask your opinion about something regarding DB design. I am trying to implement the following: "Any data that could be referenced by more than one table should have its own table and be joined to the table wherever it is needed."


<Table: Jobs>
1 Engineer
2 Teacher

<Table: Names&Jobs>
1 John, 1
2 Mark, 2
3 Lisa, 1

This makes sense. I can add new jobs, new people, change the name of the jobs all very easily. This design provides decoupling.

However, I am running into difficulties updating the data in my code. When the user interacts with the tables, all they know is some attribute.


Let's say the GUI allows the user to pick a name, and change the associated job. The available jobs are in a combobox or something. When the user makes a change, the code gets the name and the new job as string types. When I update the database, I need to first dig up the ID for the new job and then update the "Names&Jobs" table. So each update is a multistep process.

Is there a better way to do this? I am not an SQL expert. I'd appreciate your help.
Tuesday, March 20, 2007
The various list controls usually allow you to specify a name and an id for each list item.  When the user selects from the list, use the id instead of the name to update the database.
Mike S Send private email
Tuesday, March 20, 2007

In the combo box itself, the id for the job is the value of the entry of the combo box. So for example, the user might see "Engineer" as an option and select it, but the job id "1" actually gets passed back to the server, not "Engineer". At that point, you only have to update the Names&Jobs table.

Taking a step back, you basically do a SELECT on Jobs, get both the id and the name, display the name in the combo box, set the value to its corresponding id, and then INSERT the id into Names&Jobs.
Tuesday, March 20, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz