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@postgresql.org so that your
>        message can get through to the mailing list cleanly