Re: Form saving, db question ?
vlado wrote:
To be sensire ID in the problem domain represents nothing. It's just and
indentifier to make one entity unique form another. When I persist
entity in DB I'm fetching it by his database generated(surogate) ID. The
problem is that my current DBA is poorly designing
our database beacause we have over 500 tables and every table has
ID(LONG-surogate), and niether of them doesn't use some natural primary
key like you set for Car table that it will be car field, and I don't
know why. That's the rason why
I would use ID(surogate) in every new table that I need beacuse it is
the common practise for my DBA, but it seems that he isn't doing it
right :-(
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 ...
--
Lew