Form saving, db question ?
in [Java Programming]
|
Prev: PDF Screen Shots
Next: open all sites free now
From: vlado on 12 Jul 2010 04:32 On 12.7.2010 9:10, Lew wrote: > 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 ... > 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, you are telling me that these tables doesn't have to have surrogate keys, yes ?
From: vlado on 12 Jul 2010 04:40 On 12.7.2010 9:10, Lew wrote: > 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 ... > 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 ?
From: Lew on 12 Jul 2010 08:31 vlado wrote: > 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
From: Lew on 12 Jul 2010 08:38 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. -- Lew
From: vlado on 12 Jul 2010 09:51
On 12.7.2010 14:31, Lew wrote: > vlado wrote: >> 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. > 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 ? |