Can you save to file and edit file?

[Guest]

I have this problem with a dummy site i'm working on (for learning purposes) and i have 20 form text fields with an "add extra field" button. They are for entering sales of products, but the problem i'm having is that i use a DB, but come to realize that anything greater then 20 entries won't work if i want to use a DB. So i was wondering if you can save the information given from the form fields onto a file of some sort (like excel or pdf), but later on if i want to make a change to an entry on that file would it be possible to recall the file and make the changes accordingly without rewriting the entire thing?

Hope that makes sense. thx

[BKBK]

You intend to moonwalk back in time, mate. Databases were invented for just the purpose you describe. Some, like MySQL, have free versions.

[Dan_Bracuk]

What happens on the 21st entry?

[Guest]

Sorry the error i speak of is something else, but what it actually does is only insert the first 20 since the db only has room for 20. So now i don't know how to compensate for the extra items for the form.

Sorry for the mix up. Writing code at 3am is never good.

[Carl Von Stetten]

@cf_junkie,

When you say "the db only has room for 20", do you mean the table you are saving to only has 20 columns to store products on an order, or something like that?  If so, you need to rethink your database structure.

-Carl V.

[Guest]

The table. As far as rethink not sure what direction to take.

[Carl Von Stetten]

The common pattern for storing orders and items is to put the order-specific details in one table ("orders" maybe), and put the items in a separate related table ("orderitems" maybe).  The primary key of the "orders" table (maybe "orderID") would then be stored in a column in the "orderitems" table, and would be used as a foreign key to link back to "orders".  That way you can have any number of items associated with an order.  You also don't store a bunch of null values in your orders table for item-related columns that are not used.

-Carl V.

[Guest]

I'll give that a try. Thank you

[BKBK]

Question one: is it possible to store the information as rows in a text file or Excel file, which you can then update? Yes. ColdFusion offers plenty of ways to save the form data to file and eventually update the file. However, doing it that way will be inefficient and error-prone. In effect, you will be doing by hand what database engines have been designed to do automatically. So, stay with a database solution.

Beware you don't go creating a new OrderItems table only because you've run out of columns. If I understand it correctly, your situation is as follows. Your current database table has 20 columns. Each column corresponds to a form field. You now wish to extend your form to 21 fields or more. The extra fields, I would assume, are of the same type as the existing 20.

Question two: what is a possible database solution? Assuming you have appropriate database permissions, alter your existing table by adding more columns to it. It is an easy procedure. Post to the database section of this forum if you need assistance. Otherwise, ask your database administrator to do it for you.

[Dan_Bracuk]

Do not alter your table by adding more columns.  Your initial db design was a mistake.  Adding more columns is simply changing the details of that mistake.

If you don't completely understand Carl's second post, I've heard good things about the book, Database Design for Mere Mortals.

[BKBK]

Dan Bracuk wrote:

Do not alter your table by adding more columns.  Your initial db design was a mistake.  Adding more columns is simply changing the details of that mistake.

You make a mistake and attribute 20 properties to an Order. You discover later an order is best described by 28 properties. Do you solve this by renaming the new properties orderItems?

[Carl Von Stetten]

I don't believe that the OP was talking about properties of the order, but items included in the order (at least that was the impression I got from the previous entries on this thread).

-Carl V.

[BKBK]

Carl Von Stetten wrote:

I don't believe that the OP was talking about properties of the order, but items included in the order (at least that was the impression I got from the previous entries on this thread).

There is just a difference in how we call things. What I call properties of an order, you call order-specific details. See:

The common pattern for storing orders and items is to put the order-specific details in one table ("orders" maybe), and put the items in a separate related table ("orderitems" maybe).

There is no arguing with your suggestion, of course. I am just sounding a note of caution. It just might be that some of the new form fields represent further order-specific details. If so, you cannot just bundle them into a separate new table.

Every which way you look at it, a redesign is inevitable. In fact, it is quite likely that some of the existing 20 columns represent order items! Those will have to be moved to the new OrderItems table.