Form saving, db question ?
in [Java Programming]
|
Prev: PDF Screen Shots
Next: open all sites free now
From: vlado on 8 Jul 2010 07:11 I have a form like this Param type1(outputText) : value1(inpuText) Param type2 (outputText) : value2(inputText) .. . .. . and so on. And I want to save it in a database. Is it better to have one table which will have columns like this : ID, PARAM_TYPE, PARAM_VALUE 1 FORD_MUSTANG 10 2 AMG_CLK 50 and so on..... , or table that will have columns like this : ID, PARAM_TYPE_FK, PARAM_VALUE, 1 1 10 2 2 50 and so on.... where PARAM_TYPE_FK is FK to look_up table cointaing types like (FORD_MUSTANG, MERCEDES_AMG...and so on) , or to have these types in let's say enum in web controller and write just (1,2) in PARAM_TYPE_FK column without look up table. Thanks in advance.
From: Lew on 8 Jul 2010 09:53 On Jul 8, 7:11 am, vlado <vl...(a)miha.net> wrote: > I have a form like this > > Param type1(outputText) : value1(inpuText) > Param type2 (outputText) : value2(inputText) > . . > . . > > and so on. And I want to save it in a database. Is it better to have one > table which will have columns like this : > > ID, PARAM_TYPE, PARAM_VALUE > 1 FORD_MUSTANG 10 > 2 AMG_CLK 50 Wow, that didn't line up well. > and so on..... > > , or > > table that will have columns like this : > > ID, PARAM_TYPE_FK, PARAM_VALUE, > 1 1 10 > 2 2 50 > > and so on.... > > where PARAM_TYPE_FK is FK to look_up table cointaing types like > (FORD_MUSTANG, MERCEDES_AMG...and so on) , or to have these types in > let's say enum in web controller and write just (1,2) in PARAM_TYPE_FK > column without look up table. > That depends. You will need a search engine and/or Wikipedia for the following. If you are using a relational database, it is best to keep it at least at third normal form (3NF), unless it's a data warehouse, in which case the time dimension changes the idea of what constitutes a normal form. Your question seems to focus on the matter of natural keys vs. sequenced surrogate keys. What is the business-domain meaning of the "ID" column in your layout? When you do a relational database design, at least at the so-called "logical" level, you need to know what constitute the keys in your problem domain. I venture to gamble that "ID" is not part of the problem domain and therefore no part of your logical design. If you do use such a surrogate key, it will be an optimization detail of the physical implementation that has no place in the domain model. For your particular problem, I'd probably have a lookup table representing the car types to keep them consistent, and copy the actual value into the table that references them. table PARAMS: PARAM_TYPE PARAM_VALUE ------------ ----------- Ford Mustang 10 Chevy Camaro 17 table PARAM_TYPES PARAM_TYPE ------------ Ford Mustang Chevy Camaro Yes, there'd be a FK relationship between PARAM_TYPE in both tables. This is not a Java question, but Java programmers do need to know about database design. -- Lew
From: vlado on 9 Jul 2010 03:09 I'm using a relation database, and ID in these tables is used as a primary key. What do you mean by you will have a lookup table with car types to keep them consistent ? Is't lookup table bes used when let's say I hava a drop down list in my form with data that which will be rearly changed or updated ? Thanks for the answer.
From: Arved Sandstrom on 9 Jul 2010 06:06 vlado wrote: > I'm using a relation database, and ID in these tables is used as > a primary key. What do you mean by you will have a lookup table with car > types to keep them consistent ? Is't lookup table bes used when let's > say I hava a drop down list in > my form with data that which will be rearly changed or updated ? > > Thanks for the answer. My question would still be, in *your* problem is the car type (something like FORD_MUSTANG) part of the identity? Lew was trying to get at this, because it's important. If it's not part of the identity then that field is more of a free-form descriptive string; if you wish to pre-define the available values that then is still more of a user-input presentation-layer constraint, and you could use an enum mapping to a varchar field to store the actual values. In this case your ID primary key is a surrogate key and you may have no natural key at all. If OTOH the car type is part of the identity (it's all or some of a/the natural key) then you may very well still be using the ID column as a surrogate and primary key, but it's now also in your interests to ensure that the car type column is constrained (a unique index) on the data/RDBMS side. In which case the table that enforces this constraint isn't going to be the table that stores user input; hence foreign keys. In this case, as Lew said, the actual param value is in the user input table, that uses a foreign key relationship to point back at the table containing the car type natural keys. "Rarely changed or updated" isn't typically your driver in deciding data relationships, what the natural keys are, or how you'll enforce data integrity in your tables. It's definitely a factor - for example, I'd take it into account when fine-tuning entity caching in JPA - but not at this stage. AHS -- Sir James Dewar Is smarter than you are None of you asses Can liquify gases.
From: vlado on 9 Jul 2010 06:39
On 9.7.2010 12:06, Arved Sandstrom wrote: > vlado wrote: >> I'm using a relation database, and ID in these tables is used >> as a primary key. What do you mean by you will have a lookup table >> with car types to keep them consistent ? Is't lookup table bes used >> when let's say I hava a drop down list in >> my form with data that which will be rearly changed or updated ? >> >> Thanks for the answer. > > My question would still be, in *your* problem is the car type > (something like FORD_MUSTANG) part of the identity? Lew was trying to > get at this, because it's important. > > If it's not part of the identity then that field is more of a > free-form descriptive string; if you wish to pre-define the available > values that then is still more of a user-input presentation-layer > constraint, and you could use an enum mapping to a varchar field to > store the actual values. In this case your ID primary key is a > surrogate key and you may have no natural key at all. > > If OTOH the car type is part of the identity (it's all or some of > a/the natural key) then you may very well still be using the ID column > as a surrogate and primary key, but it's now also in your interests to > ensure that the car type column is constrained (a unique index) on the > data/RDBMS side. In which case the table that enforces this constraint > isn't going to be the table that stores user input; hence foreign > keys. In this case, as Lew said, the actual param value is in the user > input table, that uses a foreign key relationship to point back at the > table containing the car type natural keys. > > "Rarely changed or updated" isn't typically your driver in deciding > data relationships, what the natural keys are, or how you'll enforce > data integrity in your tables. It's definitely a factor - for example, > I'd take it into account when fine-tuning entity caching in JPA - but > not at this stage. > > AHS > 1.What do you mean by "part of identity" ? 2.Other thing is that on every project we have practise to have lookup tables , like country that have for example ID, NAME columns, so I can't use table without primary key (ID). |