Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
create fields in a database at running time
- Thread starter elibb
- Start date
salmonsteak
Technical User
I've done quite a bit of work using VB6 and SQL Server 7.0 and cannot honestly say that adding a column to a table through your VB front end is possible, although a good question. In most cases your going to want to fully define the attributes of your DB before communicating with it through VB. What kind of application are you writing that would require you to do this? Let me know an I'll see if I can help you get around it and still acheive what you are trying to do.
- Thread starter
- #3
i need a list of prices, i have some elements, but accordint to the client, the price is different. i know i can save them all in one table, like putting an id to the list, and then setting all the prices for every element, but that table is going to get very very big, so i was thinking that maybe i can add fields, and each field is a new list of prices... any ideas?
thank you
Eli
thank you
Eli
salmonsteak
Technical User
If you are familiar with database design and implementation than you'll understand it when I say you do not want to have multiple fields called price this makes things redundant and redundancy is something we don't want. You want to have one feild that stores the price, in fact if you want you can add that field to the same table that stores your element or product.
You should not have to worry about that table getting really big, your using SQL Server 7.0 which is equipped to handle large amounts of data, this may be an issue with something like MS Access.
In regards to prices changing frequently, the great thing about fields is you can update them, which is essentially what your VB front end will handle. Let's look at it this way if you have a product let's say it's a soccerball and the initial price of that soccerball is $10.99 chances are you'll have a table that stores data about your products. In this table you'll have a Primary Key representing each product, you'll have a field that stores the product name, and you'll have a field that stores the price. Now let's say you change the price of the soccerball to $15.99 then all you have to do is update the price field that is related to the soccerball to reflect the new price, and this is what you'll do with your VB front end.
Hopefully this sheds some light on the situation. Again you do not want to have multiple fields storing prices. If you are familiar with Codds rules than you will understand why. If you have any more questions just post them.
You should not have to worry about that table getting really big, your using SQL Server 7.0 which is equipped to handle large amounts of data, this may be an issue with something like MS Access.
In regards to prices changing frequently, the great thing about fields is you can update them, which is essentially what your VB front end will handle. Let's look at it this way if you have a product let's say it's a soccerball and the initial price of that soccerball is $10.99 chances are you'll have a table that stores data about your products. In this table you'll have a Primary Key representing each product, you'll have a field that stores the product name, and you'll have a field that stores the price. Now let's say you change the price of the soccerball to $15.99 then all you have to do is update the price field that is related to the soccerball to reflect the new price, and this is what you'll do with your VB front end.
Hopefully this sheds some light on the situation. Again you do not want to have multiple fields storing prices. If you are familiar with Codds rules than you will understand why. If you have any more questions just post them.
CajunCenturion
Programmer
elibb,
you stated:
i need a list of prices, i have some elements, but accordint to the client, the price is different. i know i can save them all in one table, like putting an id to the list, and then setting all the prices for every element, but that table is going to get very very big, so i was thinking that maybe i can add fields, and each field is a new list of prices... any ideas?
The first question is this: does this mean that each individual item has multiple prices, or, a price list for each item?
If the answer to this is YES, then I would suggest that you create a separate table for prices, with the product_id, perhaps a price_type_code, and the price itself. You have a one-to-many relationship between a product and a list of prices, and you should never try to encapsulate a 1-to-many relationship inside a single table.
If the answer is NO, then you only need one price column, which should be in the product table, since there is only one price per product.
Good Luck
------------
Select * from Users where Clue > 0
0 rows returned
you stated:
i need a list of prices, i have some elements, but accordint to the client, the price is different. i know i can save them all in one table, like putting an id to the list, and then setting all the prices for every element, but that table is going to get very very big, so i was thinking that maybe i can add fields, and each field is a new list of prices... any ideas?
The first question is this: does this mean that each individual item has multiple prices, or, a price list for each item?
If the answer to this is YES, then I would suggest that you create a separate table for prices, with the product_id, perhaps a price_type_code, and the price itself. You have a one-to-many relationship between a product and a list of prices, and you should never try to encapsulate a 1-to-many relationship inside a single table.
If the answer is NO, then you only need one price column, which should be in the product table, since there is only one price per product.
Good Luck
------------
Select * from Users where Clue > 0
0 rows returned
- Thread starter
- #6
right, an element can have many prices, thank you very much for your advice, i will have a table for elements, and another for prices, that includes the id of the elements, actually thats what i was thiknking, but i was afraid that the table will become too big.
thank you very much.
Eli
thank you very much.
Eli
If you have dba or dbo permissions on the database server, you can run the ALTER TABLE SQL command to add your column. But, like others have said here, I don't think that's a good idea. For one, it's not "relational". And secondly, it's not a good idea to allow your program to have dba priviledges.
What I've seen in other inventory mgmnt systems is that you have a table which contains item templates which hold the things which don't change about an item - description, dimensions, weight, color, etc. You then have another table that holds current quantities, location where it's stored, price paid, sale price, date item received, etc. This way when your quantity reaches zero, you don't lose all the information which describes the item.
You can have another table which keeps track of your pricing history, so you can see if item prices are trending up or down over time. I can recommend a book named "Distribution Inventory Management" by Gordan Graham, which gets into re-order points, safety stock levels, etc.
Chip H.
What I've seen in other inventory mgmnt systems is that you have a table which contains item templates which hold the things which don't change about an item - description, dimensions, weight, color, etc. You then have another table that holds current quantities, location where it's stored, price paid, sale price, date item received, etc. This way when your quantity reaches zero, you don't lose all the information which describes the item.
You can have another table which keeps track of your pricing history, so you can see if item prices are trending up or down over time. I can recommend a book named "Distribution Inventory Management" by Gordan Graham, which gets into re-order points, safety stock levels, etc.
Chip H.
- Thread starter
- #8
Thank you very much. your advice really helped me. i will make a table with the prices, and another one with the description of the elents, my question was mainly because the user is allowed to add different prices to the elementes, and an elemtn can have sevaral prices at the same time, it depends on the costumer. but i will put that in another table.
thank you very very very much
Eli
thank you very very very much
Eli
- Status
Similar threads
- Question