Обсуждение: Question about fk and uk for Database Modeler
I'm checking right now database designer way of updating / creating foreign keys from pk, and as part of this work I want to add uk / fk implementation, but I want to check some points before, because I'm not totally sure about it, please I'll appreciate your help.
1.How should I add option to choose between pk and uk as source of foreign key ?
my idea: right click over relationship let me choose source of fk: uk (allow me to choose which uk of many) or pk
2. When I create a fk some columns are added to table and
reference: http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284
3. suppose that I created a fk from a pk if I add hierarchy all fk columns become fk/pk that's fine, but if I created a fk from an uk and I add hierarchy, what should be the normal behavior?.
docs said: Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional. reference: http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284
Regards, Luis.
1.How should I add option to choose between pk and uk as source of foreign key ?
my idea: right click over relationship let me choose source of fk: uk (allow me to choose which uk of many) or pk
2. When I create a fk some columns are added to table and
MATCH matchtype right now is only MATCH FULL but I have more options to choose like: MATCH PARTIAL (is still no implemented yet or docs are outdated ) MATCH SIMPLE, . Question here is should I let the user to choose between match full and simple (not partial yet)?reference: http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284
3. suppose that I created a fk from a pk if I add hierarchy all fk columns become fk/pk that's fine, but if I created a fk from an uk and I add hierarchy, what should be the normal behavior?.
docs said: Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional. reference: http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284
Regards, Luis.
On 05/20/2011 05:26 PM, Luis Ochoa wrote: > I'm checking right now database designer way of updating / creating foreign > keys from pk, and as part of this work I want to add uk / fk implementation, > but I want to check some points before, because I'm not totally sure about > it, please I'll appreciate your help. > > 1.How should I add option to choose between pk and uk as source of foreign > key ? > my idea: right click over relationship let me choose source of fk: uk > (allow me to choose which uk of many) or pk > Fine to me. > 2. When I create a fk some columns are added to table Why? a user could create a FK with existing columns. > and MATCH matchtype right > now is only MATCH FULL but I have more options to choose like: MATCH > PARTIAL(is still no implemented yet or docs are outdated ) MATCH > SIMPLE, . Question here is should I let the user to choose between match > full and simple (not partial yet)? Right click on the relationship, and the MATCH FULL/SIMPLE choice is available in this contextual menu. > reference: > http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284 > > 3. suppose that I created a fk from a pk if I add hierarchy all fk columns > become fk/pk that's fine, but if I created a fk from an uk and I add > hierarchy, what should be the normal behavior?. I don't understand what you mean here. > docs said: Unique constraints and primary keys are not inherited in the > current implementation. This makes the combination of inheritance and unique > constraints rather dysfunctional. reference: > http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284 > This is in the case of inheritance, mostly when you can to add partitions to a table. Which TODO are you working on? -- Guillaume http://www.postgresql.fr http://dalibo.com
On Sun, May 22, 2011 at 2:10 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote:
Implemented yet.
Right, thanks for point me that, because I focused on programming and not at usability :( but right now is implemented in this way:
- User created a relationship between tables, by default of pk kind, but dependents of kind a fk column is added to destination table for each pk/uk column from source table with name tablesourcename_column.
Next user is (or will be) able to:
- After fk columns are added he can change name for whatever he likes. (implemented soon)
- Only If user select to use auto generate names, a change at table source column name automagically change destination fk name to adjust it. User will be able to select for each column what he likes auto generated name or customized (sync is implemented yet, but selection of user for customized or auto generated name will be implemented soon).
- Now after your question, I notice that a user is able to use an existing column as fk, and then I will create a fk->column mapping dialog for this goal, that allow user to choose which existing column is binded to which fk.
(implemented soon)
Suggestions?
Ok. I'll add this menu item.
Ummm my mistake bad question, probably I was sleepy.
This is: Should I allow to mark as Identifying relationship a foreign key from an Uk?
I'm working right now with files instead TODO, because I check every file for every TODO and then implemented, fixed it (with related files) , some TODOs (minimal as possible) are delayed until now this are Delayed for later:
1. dditems->figures->ddTableFigure.cpp //DD-TODO: fix all model to allow all options from http://www.postgresql.org/docs/8.1/static/sql-createtable.html
2. dditems->figures->ddTextColumnFigure.cpp //DD-TODO: Add composite column functionality by addin subcolumn for composite types, but be careful: composite types can be recursive (using inside other composite types)
Status Righ now:
- dditems->figures (probably most important TODOs for modeler were here) TODO are almost done with some related classes and some parts of draw
Regards, Luis.
On 05/20/2011 05:26 PM, Luis Ochoa wrote:Fine to me.
> I'm checking right now database designer way of updating / creating foreign
> keys from pk, and as part of this work I want to add uk / fk implementation,
> but I want to check some points before, because I'm not totally sure about
> it, please I'll appreciate your help.
>
> 1.How should I add option to choose between pk and uk as source of foreign
> key ?
> my idea: right click over relationship let me choose source of fk: uk
> (allow me to choose which uk of many) or pk
>
Implemented yet.
Why? a user could create a FK with existing columns.
> 2. When I create a fk some columns are added to table
Right, thanks for point me that, because I focused on programming and not at usability :( but right now is implemented in this way:
- User created a relationship between tables, by default of pk kind, but dependents of kind a fk column is added to destination table for each pk/uk column from source table with name tablesourcename_column.
Next user is (or will be) able to:
- After fk columns are added he can change name for whatever he likes. (implemented soon)
- Only If user select to use auto generate names, a change at table source column name automagically change destination fk name to adjust it. User will be able to select for each column what he likes auto generated name or customized (sync is implemented yet, but selection of user for customized or auto generated name will be implemented soon).
- Now after your question, I notice that a user is able to use an existing column as fk, and then I will create a fk->column mapping dialog for this goal, that allow user to choose which existing column is binded to which fk.
(implemented soon)
Suggestions?
Right click on the relationship, and the MATCH FULL/SIMPLE choice is
> and MATCH matchtype right
> now is only MATCH FULL but I have more options to choose like: MATCH
> PARTIAL(is still no implemented yet or docs are outdated ) MATCH
> SIMPLE, . Question here is should I let the user to choose between match
> full and simple (not partial yet)?
available in this contextual menu.
Ok. I'll add this menu item.
I don't understand what you mean here.
> reference:
> http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284
>
> 3. suppose that I created a fk from a pk if I add hierarchy all fk columns
> become fk/pk that's fine, but if I created a fk from an uk and I add
> hierarchy, what should be the normal behavior?.
Ummm my mistake bad question, probably I was sleepy.
This is: Should I allow to mark as Identifying relationship a foreign key from an Uk?
Which TODO are you working on?
I'm working right now with files instead TODO, because I check every file for every TODO and then implemented, fixed it (with related files) , some TODOs (minimal as possible) are delayed until now this are Delayed for later:
1. dditems->figures->ddTableFigure.cpp //DD-TODO: fix all model to allow all options from http://www.postgresql.org/docs/8.1/static/sql-createtable.html
2. dditems->figures->ddTextColumnFigure.cpp //DD-TODO: Add composite column functionality by addin subcolumn for composite types, but be careful: composite types can be recursive (using inside other composite types)
Status Righ now:
- dditems->figures (probably most important TODOs for modeler were here) TODO are almost done with some related classes and some parts of draw
Regards, Luis.
On 05/22/2011 11:02 PM, Luis Ochoa wrote: > On Sun, May 22, 2011 at 2:10 AM, Guillaume Lelarge > <guillaume@lelarge.info>wrote: > >> On 05/20/2011 05:26 PM, Luis Ochoa wrote: >>> I'm checking right now database designer way of updating / creating >> foreign >>> keys from pk, and as part of this work I want to add uk / fk >> implementation, >>> but I want to check some points before, because I'm not totally sure >> about >>> it, please I'll appreciate your help. >>> >>> 1.How should I add option to choose between pk and uk as source of >> foreign >>> key ? >>> my idea: right click over relationship let me choose source of fk: uk >>> (allow me to choose which uk of many) or pk >>> >> >> Fine to me. >> > > Implemented yet. > > >> >>> 2. When I create a fk some columns are added to table >> >> Why? a user could create a FK with existing columns. >> > > Right, thanks for point me that, because I focused on programming and not at > usability :( but right now is implemented in this way: > > - User created a relationship between tables, by default of pk kind, but > dependents of kind a fk column is added to destination table for each pk/uk > column from source table with name tablesourcename_column. > > Next user is (or will be) able to: > - After fk columns are added he can change name for whatever he likes. > (implemented soon) > - Only If user select to use auto generate names, a change at table source > column name automagically change destination fk name to adjust it. User will > be able to select for each column what he likes auto generated name or > customized (sync is implemented yet, but selection of user for customized or > auto generated name will be implemented soon). > > - Now after your question, I notice that a user is able to use an existing > column as fk, and then I will create a fk->column mapping dialog for this > goal, that allow user to choose which existing column is binded to which fk. > (implemented soon) > > Suggestions? > Nope. This new dialog is not important right now. Keep in mind; basic functionalities for the first patch. Only TODOs. >>> and MATCH matchtype right >>> now is only MATCH FULL but I have more options to choose like: MATCH >>> PARTIAL(is still no implemented yet or docs are outdated ) MATCH >>> SIMPLE, . Question here is should I let the user to choose between match >>> full and simple (not partial yet)? >> >> Right click on the relationship, and the MATCH FULL/SIMPLE choice is >> available in this contextual menu. >> > > Ok. I'll add this menu item. > OK. >>> reference: >>> >> http://www.network-theory.co.uk/docs/postgresql/vol1/CREATETABLE.html#SEC284 >>> >>> 3. suppose that I created a fk from a pk if I add hierarchy all fk >> columns >>> become fk/pk that's fine, but if I created a fk from an uk and I add >>> hierarchy, what should be the normal behavior?. >> >> I don't understand what you mean here. >> > > Ummm my mistake bad question, probably I was sleepy. > > This is: Should I allow to mark as Identifying relationship a foreign key > from an Uk? > Yes. > >> Which TODO are you working on? >> >> > I'm working right now with files instead TODO, because I check every file > for every TODO and then implemented, fixed it (with related files) , some > TODOs (minimal as possible) are delayed until now this are Delayed for > later: > > 1. dditems->figures->ddTableFigure.cpp //DD-TODO: fix all model to allow > all options from > http://www.postgresql.org/docs/8.1/static/sql-createtable.html > OK. > 2. dditems->figures->ddTextColumnFigure.cpp //DD-TODO: Add composite column > functionality by addin subcolumn for composite types, but be careful: > composite types can be recursive (using inside other composite types) > OK. > Status Righ now: > - dditems->figures (probably most important TODOs for modeler were here) > TODO are almost done with some related classes and some parts of draw > OK, great. Thanks. -- Guillaume http://www.postgresql.fr http://dalibo.com