Thread: db question - dynamic fields in db

db question - dynamic fields in db

From

"a"

Date:

01 July 2006, 11:44:19

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

Re: db question - dynamic fields in db

From

Ron Johnson

Date:

01 July 2006, 12:46:52

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

Re: db question - dynamic fields in db

From

"a"

Date:

02 July 2006, 22:30:28

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

Re: db question - dynamic fields in db

From

Ron Johnson

Date:

02 July 2006, 23:39:20

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