Form saving, db question ?

[vlado]

As Arne pointed out earlier, there's a good argument for LONG id
fields to serve as the primary key for entity tables.

That doesn't mean every table should have one.

Certain tables represent entities, like CAR in your example. (I took
the liberty of picking my own table names here.) I'll use LONG ID
columns for entities here though I won't always choose to do that
every time.

CREATE TABLE CAR
(
ID SERIAL NOT NULL PRIMARY KEY,
VIN VARCHAR (17) UNIQUE,
MAKE VARCHAR (60),
MODEL VARCHAR (60),
YEAR INTEGER
);

"MAKE" and "MODEL" can be foreign keys to like-named tables. Those
referenced tables are usually lookups for valid values rather than
entities in their own right. They can have ID fields, but the short
ones (and most are short) can also have values directly in the
columns, denorming the value into the referencing table. This saves
on JOIN overhead in SELECTS that use that column.

Obviously VIN is the natural key. The ID surrogate key allows changes
to the natural key with less stress to inter-table relationships, if
done correctly.

Some SQL dialects support an enum type similar to the Java enum. Then
it's a choice of how you enforce the values and whether you need
extensibility.

Point being that a lookup table can have a serial ID but probably
doesn't need one.

A linking table should not have a surrogate key. A linking table
enforces structure - many-to-many relantionships between tables. It
rarely needs or has more than the foreign key columns that tie other
tables together. The primary key is the multi=column concatenation of
the foreign key columns. The table's purpose is to support JOIN
clauses, and extra ID columns interfere with that.

ID columns in entity tables keep join-table keys relatively compact:

CREATE TABLE cardriver -- many cars to many drivers
(
car INTEGER FOREIGN KEY REFERENCES car (id),
driver INTEGER FOREIGN KEY REFERENCES driver (id),
PRIMARY KEY (car, driver)
);

Multi-way JOIN SELECTs that go from, say, car to residence via driver
but displaying columns only from car and residence can skip the driver
entity table altogether and use just linking tables in the middle.

SELECT car.*, res.* FROM car
JOIN cardriver ON car.id = cardriver.car
JOIN driverresidence dres ON cardriver.driver = dres.driver
JOIN residence res on dres.residence = res.id
WHERE ...

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


Can I model it the way garage/table is modeled ? And how come i 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 ?

 

[Lew]

Regarding to the table :

CAR
(
ID SERIAL NOT NULL PRIMARY KEY,
VIN VARCHAR (17) UNIQUE,
MAKE VARCHAR (60),
MODEL VARCHAR (60),
YEAR INTEGER
); , if I make MAKE, MODEL and YEAR FK to the separate tables MAKE,
MODEL, YEAR,

Why would you have a table for YEAR and not just an INTEGER?

you are telling me that these tables doesn't have to have
surrogate keys, yes ?

Not necessarily. For example you might prefer the value (e.g., MAKE (make))
itself to be the key, so that you can use the value directly in the
referencing table. You might not even have a foreign-key relationship, just a
CHECK constraint, if you wish to preserve formerly-valid values that are no
longer in the lookup table.

 

[Lew]

And I have one similar table :

FUTURE STATISTICS: (current year) 2010 (inputText)

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.

 

[vlado]

Why would you have a table for YEAR and not just an INTEGER?


Not necessarily. For example you might prefer the value (e.g., MAKE
(make)) itself to be the key, so that you can use the value directly
in the referencing table. You might not even have a foreign-key
relationship, just a CHECK constraint, if you wish to preserve
formerly-valid values that are no longer in the lookup table.

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 ?

 

[vlado]

And I have one similar table :

FUTURE STATISTICS: (current year) 2010 (inputText)

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 ?

 

[Lew]

     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]

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)

 

[vlado]

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

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 ?

 

[Lew]

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]

     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

You can look up the constants from the lookup tables to populate your
GUI widgets.

DB i [sic] will have to define FK value before persist.

That's if you use foreign keys. You might not for lookup values; that
was the point of the post.

Then I will have the same values in DB defined twice, is it ok ?

Yes, though not "defined" but "referenced" twice.

 

[vlado]

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

You can look up the constants from the lookup tables to populate your
GUI widgets.

DB i [sic] will have to define FK value before persist.

That's if you use foreign keys. You might not for lookup values; that
was the point of the post.

Then I will have the same values in DB defined twice, is it ok ?

Yes, though not "defined" but "referenced" twice.

Regarding to lwhat you said about "

You can look up the constants from the lookup tables to populate your
GUI widgets.

"
Yes, that's ok when i have just one page /form to persist to
database, but I have 3 or 4 similar forms, let's say in garage/car
example if i have in
first table range of year from 2005-2009 to persist , and the second
form saves years range 2006-2009. That's the probem, because year's
interect. How can I
load data from lookup tables and distinguish them for two forms if my
data in lookup table will be 2005, 2006, 2007, 2008, 2009(only one
column). I need to have one more
column to tell me for which form it is in my lookup table, or no ?

Thanks.

 

[Lew]

     Yes, that's ok when i have just one page /form to persist to
database, but I have 3 or 4 similar forms, let's say in garage/car
example if i have in
first table range of year from 2005-2009 to persist , and the second
form saves years range 2006-2009. That's the probem, because year's
interect. How can I
load data from lookup tables and distinguish them for two forms if my
data in lookup table will be 2005, 2006, 2007, 2008, 2009(only one
column). I need to have one more
column to tell me for which form it is in my lookup table, or no ?

I wouldn't put years in a lookup table in the first place. I'd most
likely use surface-edit validation (in Java for Java-based systems) on
the form itself to constrain the year ranges, but not enforce that in
the data layer.

There's always a decision as to where to enforce data rules. You have
surface edits, middleware logic and data constraints - which you
choose for any given rule is a matter of art.

 

[vlado]

I wouldn't put years in a lookup table in the first place. I'd most
likely use surface-edit validation (in Java for Java-based systems) on
the form itself to constrain the year ranges, but not enforce that in
the data layer.

There's always a decision as to where to enforce data rules. You have
surface edits, middleware logic and data constraints - which you
choose for any given rule is a matter of art.

Ok, but let's say car type, or model could be put in lookup table
and joined to CAR table through FK key, but also it could be set
directly into CAR table (type, model columns) ?

 

[Arved Sandstrom]

Ok, but let's say car type, or model could be put in lookup table
and joined to CAR table through FK key, but also it could be set
directly into CAR table (type, model columns) ?

You could. I have done fairly substantial (as in several years worth of)
maintenance work on a government motor vehicle registry J2EE
application. In that application vehicle make is a separate table and
the vehicle table, among others, references make by FK. Vehicle model,
OTOH, is just a plain String in the vehicle table. That's what makes
sense for us.

AHS