Storing Objects from JAVA in SQL as a table

[impaler_prince]

We are writing a program in JAVA that thru a series of questions helps the user build a computer. This is being done with Radio buttons. We are wanting to know if it is possible to store the selections as objects in the database. So that is the user wants to come back and completer the transaction at a later time. It will have saved all the info.

We were thinking that we could create tables with just true false.
Just looking for ideas of how we could make this work.

[griswolf]

Since you know all the questions in advance, you can create a single table with one column per answer (some answers must be nullable so that the user can choose options that would not be consistent (for instance, if you choose to build a Linux box, then you cannot choose some Windows utilities, or vice versa). In addition, you would want a foreign key to a 'user' table that keeps track of the user id, and whatever else (email? address? etc) you want to know about the user.
The type of each column is either a small integer (each radio box would have a range of possible index values) or a varchar (each radio box would have an associated string value)

The advantage of this schema is speed and transparency. The disadvantage is a lack of flexibility if you decide to change (particularly add to) the questions.

[impaler_prince]

Would it be easier to create a variable that stores all the choices. Then the user can choose to save by creating a user name and password (e-mail ect...) Then save all the info stored under the instance of the variable created for that user to the database under the user id.

[griswolf]

If I understand you, you want to serialize a Java class and store that data on the database. That could work, but not well.

If I misunderstood your intent and you intend to store the various parts of the "a variable" in independent columns of the table, then go for it.

The reason to store the various aspects of the user's choice in distinct columns is that you get access to the power of the SQL language from doing it that way: A significantly good thing. Depending on what you use to mediate between Java and the database, you may get this kind of detail for very small extra effort.

[~s.o.s~]

Using an ORM seems like a good fit for your problem. One approach would be:

• Create a User table which contains all the users of your applications and has a primary key (e.g. userId)
• Create a ComputerConfig table which stores the codes for all the parts of your computer as selected by the user. Sample column would be hdd, graphics card, processor, mobo etc. The values for these columns would come from their respective master tables which stores the list of all models for a given part along with misc information
• Create a UserSelection table which maintains a mapping between the User and the ComputerConfig entries.
• Create respective master tables for the different parts which would be pre-populated by you based on the goods you plan on providing to your customers. Also make sure that the radio buttons in your application for different parts have the value of the unique identifier for that part.

After the infrastructure is all setup based on the JPA provider you would be using, all you'd need to do is call persist on the UserSelection class and all the relevant details would be added/updated.

In case you are constrained and can't use a JPA implementation, you'd have to implement all the boilerplate code yourself though the logic effectively remains the same.