Database Design Tips

The first step to building a good application is to design it well before starting. I always find ER diagrams on paper help. I always design the database before I design the user interface.

Relationships

You need to create a postgresql database. Releationships need to be defined for BondDB to work correctly. Use forign keys/constraints to define these relationships. Things like default values need to be also be specified, as Bond will pick these up and insert them onto the form for you.

CREATE TABLE "payment" (
      "id"            serial primary key,
      "paymenttypeid" integer NOT NULL 
                  constraint ref_paymenttype references paymenttype (id),
      "memberid"      integer NOT NULL
                  constraint ref_member references member (id),
      "membershipid"  integer
                  constraint ref_membership references membership (id),
      "paymentdate"   date default date(now()),
      "amount"        double precision default '0',
      "note"          text
    ); 

This is an example table, the constraint components define the relationships between this table and other tables to allow BondDB to pick up the relationships. Payment would have a one to many relationship with paymenttype, member and membership tables.

Its a good idea to have your primary key as the first item in each table. The system will fall back to assuming the first record is the id sometimes if it doesn’t know from the database.

Casual Relationships

Comments placed on database fields are used to define casual relationships in Bond. Casual relations are suggest relationships between 2 different fields. For example if you had a sales form, and in the sames from you had a price. You wanted the price to be obtained from the products form, but you want to still be edit the sale price, so you don’t want a fixed constraint relationship. This is where the casual relationships come in. It defines that one field is slightly dependent on another field, and it should get its default value from that field.

In the example of a sales form, to create a casual relationship use the following syntax. There is another example in the createdb.sql script for BondDB of this with test code to support and demonstrate how it works.

    COMMENT ON sales.price IS '1;products.productprice'

Views

Views in Bond are treated just the same as tables. It can be very useful features for linking parts of your user interface to views instead of tables. A dropdown box or a columned list could use a view created in the database to define what fields, or calculate certain fields to display. For example if you want a totals column in a GtkCList then you would create a view for the SQL statement you would use to generate the fields you want. For lone dropdown boxes, which are on a form with no specific datasource (ie on a report selection menu) you would use a view to populate the dropdown box with the values you want.