Re: how to update portion of the varchar type data

hkgirlygirl_at_aol.com (Hkgirlygirl) wrote in message news:<20021129173625.29378.00008214_at_mb-mg.aol.com>...
> One of the project requirements that I am designing require the database
> to keep track of, example, quantity of
> different item numbers, however, the exact number
> of item numbers is unknown , so to avoid keep asking our DBA
> to add columns, I think of using a varchar type field and keep all different
> items.
> e.g column 1-3 of varchar(30) stores item 1 data, column 4-6 stores item 2 data
> etc etc..

The most common way to handle this sort of situation would be to have a separate table with two columns-- your table's primary key and the item number. This table would have 1 row in it for every item number.

For example, imagine I have an ORDER table and an order will consist of a number of items. I'd create tables like

ORDER
  order_id integer -- PK
  customer_id integer -- FK relationship to CUSTOMER table

ORDER_ITEMS
  order_id integer -- FK relationship to ORDER table   item_number integer

To see all the items for a particular order, you'd simply join these tables on order_id. If you want to store information about items in addition to the item number, one would most commonly create an ITEMS table and change ORDER_ITEMS.item_number to be a FK into the ITEMS table.

Alternately, you could declare item_numbers as a varray or nested table within your table. You'd be able to access item_number[0] through item_number[n] directly.

Justin Cave Received on Wed Dec 04 2002 - 00:57:34 CST