Re: db question - dynamic fields in db

you mean there are 3 tables
the list of items purchased - this is what i am asking for

the list of items purchased
What is difference between
T_SALES_CC_DETAIL and > T_SALES_DETAIL
thanks a lot ron
-----------------------------------------------------------------------------------------
> T_SALES_CC_DETAIL
> - -----------------
> SALES_ID INTEGER PRIMARY KEY
> FOREIGN KEY (T_SALES_HEADER.SALES_ID),
> CC_NUMBER CHAR(16),
> EXPIRE_DATE CHAR(6)
>
> T_SALES_DETAIL
> - --------------
> SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID),
> TRAN_SRLNO SMALLINT,
> INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID),
> QUANTITY SMALLINT,
> SALE_AMOUNT NUMERIC(10,2)
> PRIMARY KEY (SALES_ID, TRAN_SRLNO)

Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> a wrote:
> > hi
> > i want to know
> > how to add a list of items to a database
> > it is basically text, but different fields
> > but the problem is i dont know how many fields are there before hand
> >
> > so i m not sure how to store them in the db
> > sometime i need to store 10 elements and some other times 5
> > thanks a lot
>
> MUMPS has repeating fields, I think. :) Pick definitely does...
>
> Seriously, though, what you need to do is to put that section of the
> database into "1st Normal Form".
>
> For example, a "sales record" has an sales id number, customer name,
> transaction date/time, store number, cash_credit flag, credit card
> number, reversal/adjustment flag, and the list of items purchased.
>
> So, this is how the tables would look:
>
> T_SALES_HEADER
> - --------------
> SALES_ID INTEGER PRIMARY KEY,
> CUST_ID INTEGER FOREIGN KEY (T_CUSTOMER_NAME.CUST_ID),
> TRAN_DATE DATE,
> TRAN_TIME TIME,
> STORE_ID SMALLINT,
> EMPLOYEE_ID INTEGER,
> CASH_CREDIT_FL CHAR(1),
> IS_REVERSAL_FL CHAR(1),
> IS_REVERSED_FL CHAR(1),
> XREF_SALES_ID INTEGER
>
> T_SALES_CC_DETAIL
> - -----------------
> SALES_ID INTEGER PRIMARY KEY
> FOREIGN KEY (T_SALES_HEADER.SALES_ID),
> CC_NUMBER CHAR(16),
> EXPIRE_DATE CHAR(6)
>
> T_SALES_DETAIL
> - --------------
> SALES_ID INTEGER FOREIGN KEY (T_SALES_HEADER.SALES_ID),
> TRAN_SRLNO SMALLINT,
> INVENTORY_ID INTEGER FOREIGN KEY (T_INVENTORY.INVENTORY_ID),
> QUANTITY SMALLINT,
> SALE_AMOUNT NUMERIC(10,2)
> PRIMARY KEY (SALES_ID, TRAN_SRLNO)
>
> - --
> Ron Johnson, Jr.
> Jefferson LA USA
>
> Is "common sense" really valid?
> For example, it is "common sense" to white-power racists that
> whites are superior to blacks, and that those with brown skins
> are mud people.
> However, that "common sense" is obviously wrong.
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.3 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iD8DBQFEppjZS9HxQb37XmcRApNQAJ9L3GZCxVj1pUuCioId5QkpOp7FlACeJQkY
> JDIoYyAdLvanH9g7JMyAZJM=
> =jufR
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly