Re: Digest for list cdbi-talk

[prev] [thread] [next] [Date index for 2005/02/10]

Hi Andreas. You are correct. I always pull at least one of the TABLE=20
primary keys into the VIEW.

For instance, if I set up a VIEW that  showed customers and their=20
orders, some customers would be duplicated, but orders would not. My=20
VIEW would show the order table's primary key, customer info from=20
customer table and order info from order table. My RULEs would take=20
care of updates to the VIEW, such as adding a 'shipped on' date or a=20
tracking number. The RULES would need the order table's primary key to=20=

do this so it would be the first column in the VIEW. There is no reason=20=

to update customer info from such a screen, so updates on customer info=20=

would not be addressed by this VIEW.

This is how I do de-normalization. Instead of removing key=20
relationships I set up VIEWs and never use the TABLEs directly. That=20
way I am always using de-normalized data but I still have a completely=20=

normalized database structure.

If I did find a reason to update the customer from this VIEW, I could=20
set up a RULE that looked up the customer primary key by SELECTing the=20=

customer primary key using the order primary key and then doing the=20
update, or just add a second primary key column and use that directly.=20=

I'd recommend you do the former when the VIEW has been in use and you=20
don't want to break anything that depends on the columns not changing,=20=

the latter otherwise.


Justin Tocci
Fort Wayne, IN



> Justin Tocci wrote:
> | [...]
> |
> | Andreas Fromm asked about primary keys being missing in VIEWs. I=20
> always
> | have at least one primary key in my VIEWs, otherwise it would be
> | difficult to do anything but SELECT. Mostly, when combining two=20
> TABLEs
> | in one VIEW, I still only need one primary key because I can follow=20=

> the
> | relationship to edit the TABLE that doesn't have it's primary in the=20=

> VIEW.
> |
> What do you mean with "I always have at least one PK in my VIEW"? Do=20=

> you
> mean that you always define one of the table's PK as one column of the
> views?
>
> - --
> Gr=FC=DFe,
>
> Andreas Fromm=