Storing Information in Database

[mrstuff68]

I am getting ready to start building an application and I am kind of stuck on how to store information in the database. I currently have one table with all of the user info: username, password, name, address, email, etc. When a person registers they fill in all the information above and then they need to answer a few questions, for example the types of income that they receive. Under this category I would have: Wages, Food stamps, Disability, Alimony, and Other. I would like these categories to be stored in my database as a separate table, so that they show up for everyone. And when someone returns to the site only show information pertaining to the categories the registrant answered. But when someone chooses Other they get the option to enter in a value. Now what I would like to do is if someone enters Other, have that added to the database and make it a new option for the registrants following that person. So say someone puts in Social Security for Other, the people after that registrant should have the following choices: Wages, Food stamps, Disability, Alimony, Social Security, and Other.

Does anyone have any suggestions for this?

Your help is greatly appreciated.

[kfcSmitty]

Which DBMS are you using?

MS SQL, MySQL, Oracle, Access, etc?

You could either do it with a stored procedure, or you could write it in the language you're running the SQL from.

Basically do a search for the record in the "Categories" field and if the recordset doesn't return anything, then insert it in to the table.

[mrstuff68]

I am either going to be usiny MySQL or Access for this project and it will all be written in Java.

[kfcSmitty]

So you can use JDBC to connect. A page with info on mysql and jdbc can be found here


And the recordset can be read like this:

Statement stmt = con.createStatement();
    
rs = stmt.executeQuery("Select * from categoryTable where Category = '" + catStringValue + "';");
 
if (rs == null)
 stmt.executeUpdate("INSERT INTO CategoryTable VALUES ('" + catStringValue + "');");

Note that the above code has not been verified, but it should get you started.