Re: db question - dynamic fields in db

-----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-----