Обсуждение: db question - dynamic fields in db
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 a
-----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-----
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
-----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-----