Form saving, db question ?
in [Java Programming]
|
Prev: PDF Screen Shots
Next: open all sites free now
From: vlado on 12 Jul 2010 09:57 On 12.7.2010 14:38, Lew wrote: > vlado wrote: >> And I have one similar table : >> >> FUTURE STATISTICS: (current year) 2010 (inputText) >> >> ------------------------------------------------------ >> | 2011 | 2012 | 2013 | 2014 | >> ------------------------------------------------------- >> Salaries | 200.000 | | | | >> --------------------------------- -------------------- >> Expenses | 15.000 | 20.000 | | | >> ------------------------------------------------------- >> Amortization | 200.00000 | | | | >> >> etc.... > > That looks like more than one table to me - tables that relate to an > entity ("garage"?) holding columns like "( garage FK, category > VARCHAR, amount MONETARY )". > >> Can I model it the way garage/table is modeled ? > > Looks like the result of a report SELECT rather than a permanent table > to me. > >> And how come i [sic] can't >> use garage table in the previous example as parent table, and car as >> child, becaouse it's obvious that one garage will have mulitple cars ? > > You could. But should you? > > Columns in an entity table are to model inherent attributes of the > entity. The relationship between a car and a garage you'd probably > want to make more transitory, and treat cars as first-class entities. > Can't a car park in more than one garage? > > The question is whether garage:car is one:many or many:many. Even > with one:many you might choose to make a garagecar linking table. > Yes in the example of car/garage many to many is possible, but I was asking about FUTURE statistics. One specific value ( let's say salaries for 2010 (200.000)) belongs to one client and one current year, so can i make in this example one to many relationship, one table FORM_NAME for example which has current year info and client info, and FORM_VALUES table which has table values ?
From: Lew on 12 Jul 2010 13:12 vlado wrote: > Can you please explain what did you mean by "if you wish to > preserve formerly-valid values that are no longer in the lookup table" ? > , and in the table CAR what does column MAKE means > "MAKE" is the standard term for the brand name of the car, like "Ford" or "Chevrolet". "MODEL" would be the car model, like "Mustang" or "Camaro". These are the standard terms used when discussing automobiles. Let's say you have two tables, "car" and "make": CREATE TABLE car ( id SERIAL PRIMARY KEY, vin VARCHAR(17) UNIQUE, make VARCHAR (60) -- FOREIGN KEY REFERENCES make( make ) ); CREATE TABLE make ( make VARCHAR (60) PRIMARY KEY ); If the "car.make" column is a FK to the "make" lookup table you cannot get rid of a "make" from the lookup table until all referencing records in "car" go away. The lookup table would have to allow new "car" records to refer to, say, "Saturn". If "car.make" is not a foreign key, but enforced by an INSERT trigger or middleware business logic only at INSERT time, then you can force correct entries at time of insert, letting "Saturn" in through 2009. Later you can delete the "make" entry, thus preventing any future "Saturn" cars, without deleting the old "Saturn" entries from "car". -- Lew
From: Lew on 12 Jul 2010 13:22 vlado wrote: > Yes in the example of car/garage many to many is possible, but I was > asking about FUTURE statistics. One specific value ( let's say salaries > for 2010 (200.000)) belongs to one client and one current year, so can i > make in this > example one to many relationship, one table FORM_NAME for example which > has current year info and client info, and FORM_VALUES table which has > table values ? > Add a DATE or TIMESTAMP column to the garagecar linking table, or even two columns (PARKWHEN, LEAVEWHEN) and make the PARKWHEN column part of the primary key. Then you can aggregate over time using well-crafted SELECT queries for any conceivable combination of periods. SELECT garage.name, car.vin, EXTRACT( YEAR FROM car.parkwhen ) as YEAR, EXTRACT( MONTH FROM car.parkwhen ) as MONTH, SUM( garagecar.price ) as PRICE FROM garage NATURAL JOIN garagecar NATURAL JOIN car GROUP BY garage.name, car.vin, EXTRACT( YEAR FROM car.parkwhen), EXTRACT( MONTH FROM car.parkwhen) ORDER BY garage.name, car.vin, EXTRACT( YEAR FROM car.parkwhen), EXTRACT( MONTH FROM car.parkwhen) -- Lew
From: vlado on 12 Jul 2010 15:19 On 12.7.2010 19:12, Lew wrote: > vlado wrote: > >> Can you please explain what did you mean by "if you wish to >> preserve formerly-valid values that are no longer in the lookup table" ? >> , and in the table CAR what does column MAKE means >> >> > > "MAKE" is the standard term for the brand name of the car, like "Ford" > or "Chevrolet". > > "MODEL" would be the car model, like "Mustang" or "Camaro". > > These are the standard terms used when discussing automobiles. > > Let's say you have two tables, "car" and "make": > > CREATE TABLE car > ( > id SERIAL PRIMARY KEY, > vin VARCHAR(17) UNIQUE, > make VARCHAR (60) -- FOREIGN KEY REFERENCES make( make ) > ); > > CREATE TABLE make > ( > make VARCHAR (60) PRIMARY KEY > ); > > If the "car.make" column is a FK to the "make" lookup table you cannot > get rid of a "make" from the lookup table until all referencing > records in "car" go away. The lookup table would have to allow new > "car" records to refer to, say, "Saturn". > > If "car.make" is not a foreign key, but enforced by an INSERT trigger > or middleware business logic only at INSERT time, then you can force > correct entries at time of insert, letting "Saturn" in through 2009. > Later you can delete the "make" entry, thus preventing any future > "Saturn" cars, without deleting the old "Saturn" entries from "car". > > -- > Lew > Ok, but is it ok to use look-up tables only if I have some form of drop down menu on the web and then I could use them, because in our applications I have seen lookup tables only for that purpose ? If I have example like this above, I will first have to fill lookup table with some values (Saturn....), and then when saving one record to DB define constants on let's say web, business or data tier that will have the same value as lookup table, because when saving record in DB i will have to define FK value before persist. Then I will have the same values in DB defined twice, is it ok ?
From: Lew on 12 Jul 2010 15:22
vlado wrote: > Yes in the example of car/garage many to many is possible, but I was > asking about FUTURE statistics. One specific value ( let's say salaries > for 2010 (200.000)) belongs to one client and one current year, so can i > make in this > example one to many relationship, one table FORM_NAME for example which > has current year info and client info, and FORM_VALUES table which has > table values ? > You need to expand table "garagecar" a bit to include DATE or TIMESTAMP information and price for time in. (I'll invent parts of your domain model that you haven't explained, but only for the sake of illustration. YMMV.) CREATE TABLE garagecar ( garage INTEGER NOT NULL REFERENCES garage (id), car INTEGER NOT NULL REFERENCES car (id), parkwhen TIMESTAMP NOT NULL, leftwhen TIMESTAMP, parkfee NUMERIC (17, 5) PRIMARY KEY (garage, car, parkwhen) ); Then you aggregate time and price information in a SELECT to produce the kinds of reports you describe: SELECT garage.name, car.vin, EXTRACT( YEAR FROM parkwhen ) AS YEAR, EXTRACT( MONTH FROM parkwhen ) AS MONTH, SUM( parkfee ) AS price FROM garage NATURAL JOIN garagecar NATURAL JOIN car GROUP BY garage.name, car.vin, EXTRACT( YEAR FROM parkwhen ), EXTRACT( MONTH FROM parkwhen ) ORDER BY garage.name, car.vin, EXTRACT( YEAR FROM parkwhen ), EXTRACT( MONTH FROM parkwhen ) ; -- Lew |