Re: db question - dynamic fields in db

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Correct, 3 tables.  T_SALES_DETAIL records are "line items", the
stuff the customer bought.  There is only a T_SALES_CC_DETAIL record
if the transaction happens to use a credit card.

a wrote:
> 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:
> 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

iD8DBQFEqINCS9HxQb37XmcRAu2AAKDh7IqlCpIafZdZ+wDdujOyaAMXewCfQcYA
2+UKYtVQie2GMfoZ6JHs9p0=
=STKu
-----END PGP SIGNATURE-----