relational database questions

[Josh Davenport]

Hi gef. Welcome back.

I would argue a few points from previous posts.

1) Panorama is a relational database, and can be programmed as a  
relational database. Panorama does not have cascading deletes or  
cluster locking or transaction rollback. The lack of cluster locking/ 
transaction rollback can be a limiting factor, however I seriously  
doubt that it will be limiting compared to filemaker.

2) There is no visual mapping between key fields in Panorama.  
However, you can have as much logical linking between key fields as  
you like. Additionally, there are many real life instances where you  
want a semi solid relational mapping, because making the logic overly  
formalized is only limiting. IF you have a solid relational  
understanding, you can make Panorama as relational as you want.

3) Having said all that, Panorama does not want to be programmed as a  
strictly relational database because thats not where its core  
strengths are. Instead, it wants to be programmed as an object- 
relational database. For example, it is common to use a field in a  
primary table to store the data of a related table as a text array.

You can easily place the customer name on a sales form via a lookup  
using a key.

You can easily post data from a form in one table to a different  
table, it's just that you must do it explicitly. However, this is  
often not a bad thing. Lets say that an order can't be placed until  
at least $100 worth of merchandise has been added to the invoice. You  
can have a field on the invoice (or use a variable) to store all the  
line items until they add up to greater than $100 dollars. Not until  
then is the "Save" button enabled. When save is pressed, you can add  
a record for each item to the "InvoiceLineItem" table. If you don't  
need to store the line items into a separate table (perhaps there is  
no need to run statistics on them) you could just store the line  
items in a single field  in the Invoice table and dispense with the  
"InvoiceLineItem" table. Later, if you need to run statistics, you  
could just load the line items from the field into a table for the  
purpose of running stats, and then discard the data. This technique  
is viable because Panorama is ram based.

The most important thing to keep in mind here is that you absolutely  
can do relational manipulations, but sometimes there is a more  
powerful way to store stuff.

-----
If you have a Customer table with id CustID:

Add a single second table:

CustDonation:

PrimaryKey: DonationID (serial # or something)
SecondaryKey (is that want its called?): CustID
DonationDate
DonationAmount
CreationDate

Nothing more is needed. You then just need to decide if you do the  
entry from within Customers or from within CustDonation
-----

I recommend against line items. I personally feel its the worst of  
both worlds, and it certainly is not relational.

However, if you are storing an array of data in a single field (not  
the built in "line items", which span multiple fields), you can  
program in a way that feels relational, and in many respects is.

Have fun. Get comfortable with the supermatrix superobject. That is  
the tool that gives the closest to your filemaker experience. And  
look into the "post" command

Josh

On Apr 29, 2008, at 12:19 PM, gef wrote:

> Hi there,
>
> I haven't programmed in panorama fro many years now, and I am back to
> it. Meanwhile I've played with filemaker which gave me some
> frustration (like how awful filemaker is with date and ranges, and
> how it's scripting really annoy me)  but which also have few
> interesting points.
>
> One is the visual mapping of a database table, another was the
> ability to have the related entry from another table on one form and
> the third was it's use of relational information, i.e. if I have a
> table of customer and a sales table, my sales table doesn't need to
> have the customer name and last name field since it fetch the
> information directly with it's relation with the client id.
>
> I am building a donor table to be added to a customer relation
> management table. I am not sure which way to go. My first idea was to
> built a table for donor, which would keep the mainuserid, create a
> donorID# and datecreated, and I would create also a table for
> donation, which would record donorID# donationID# donation$
> donationDate etc...
>
> So my questions are, first in panorama can I display the related
> information from one table in another (in the donor table form to
> display as well the related donations). I am also wondering the line
> Item use. Is this kind of against the idea of relational database ?
> and am I kind of force to the use of line item when I want to work
> with multiple item invoice or donation?
>
> Looking forward to play more with panorama !
>
>
> _______________________________________________
> Qna mailing list
> Qna at provue.com
> http://provue.com/mailman/listinfo/qna