What is the best way to store data from different forms in a database

Forum Moderators: open

Message Too Old, No Replies

What is the best way to store data from different forms in a database

NooK

1:27 pm on Sep 18, 2007 (gmt 0)

I am creating a web application where we have different forms where the user fills the data and submit such data.

It is then supposed to be stored in a databse with all the info.

Since we can have different types of order forms and need a way to store and retrieve all user current cases.

I am looking for an opinion on what is the best way to handle such info on the database.
I think the best way is to have on table for all different order types (Rather than having one table per form/order type and having to search all tables when retrieving all user cases/orders).

So what I can think of is either having one table with fields for all possible data and set NULL in the fields which don't apply for a certain form or to have just a DATA column and store the data from the forms as a string (Like "type=mobile;model=#*$!x....") and do string parsing when retrieving the data.

What is considered the best and most effective (In other words 'proper') way to do it?

Thanks

Best Regards

NooK

physics

8:41 pm on Sep 18, 2007 (gmt 0)

The most 'proper' way to do it would be to create a normalized table structure. So this probably wouldn't be like you described but rather the info is split up so there is no duplication. I.e. you have a customer_info table, a order_info table, an item_info table, an order_type table, etc for example. The order_info one is the one you seem to be focusing on. If I were designing the database I'd probably have one order_info table and leave fields null that don't apply. Then you can put meta_data about the order type in the order_type table if you want...
It might seem cleaner to make seperate tables for different types of orders but then you're going to have to keep track of those different table names, etc and imo it will be more of a headache down the line (unless the orders are very different e.g. downloadable vs delivered goods).

For more on database normalization see:
[databasejournal.com...]

NooK

8:17 am on Sep 19, 2007 (gmt 0)

Thanks. Indeed I agree that it is more comfortable to have one data for order_info but I am afraid that it'll be a big table with lot's of columns and lot's of NULL values everywhere (Because the fields differ from form to form as the type of order are different, for example, a laptop order would contain info like memory size and so on while a new user on the system would contain things such as ID, user type and so on).

Would not a table filled with NULL fields be inneficient rather than having one table per order type and getting the proper table according the type of order in the order_type table?

Best Regards

NooK

physics

9:50 pm on Sep 19, 2007 (gmt 0)

If I went the multi-table route then I would probably do one order table and then have an order_type table which linked that table to some sub_tables like order_info_laptop which had the specific fields for laptop orders. This way only the specific info is split into the different tables, not things like the order time, etc.

NooK

7:31 am on Sep 24, 2007 (gmt 0)

Exactly what I have done for the moment Physics. Thanks for the input.

Best Regards

NooK

physics

7:55 pm on Sep 24, 2007 (gmt 0)

Glad I could help NooK. Database design is tricky and there's always more than one way to do it but this should serve you well for the time being :)