most database designer, alter the db during development the

1
3118InterBase and JDataStore Database Design

• daniel magin
• better office
• germany

2
Introduction

• Database design is the base of the quality for
  your applications
• Size
• Speed
• Chance for Replication
• Multilanguage support
• Easyment for Developer incl. different Languages

3
Introduction

• Important Topics for database design are the
  combination of
• create DataBase
• Table Structures
• Field Types
• Indecies
• Stored Procedures

4
Step 1 Analyse

• Most Database designer, alter the db during
  development the software. Changing a fieldtype
  can create a lot of work in existing code.

5
Step 1 Analyse

• Document all Tables (wishes) with all Fields
• Wich fields are important for search -gt later for
  create index

6
Step 1 Structure Tables

• Sort Fields in Structures

7
Benefits of Deep Table Structures

• Size
• Example InterBase 100.000 Records
• with 50 different types

((508508)100000) / 1024 78125 KB 76.29 MB
(((32508)100.000) 50(32508)) / 1024
42208 KB 41,21 MB
my best personal compress was from 1.3 GB
database to 340 MB
8
Benefits of Deep Table Structures

• Search Time
• Select from TBL_COMMUNICATION where TYPE
  MOBILE
• Select from TBL_COMMUNICATION where ID_TYPE 5
• Search by Integer is much quicker as search by
  string !!

9
Benefits of Deep Table Structures

• Type Errors
• MOBILE ltgt Mobile ltgt mobile .
• CREATE INDEX IND_COMMTYPE on TBL_COMMUNICATIONS
  (TYPE)
• Select from TBL_COMMUNICATIONS where
  upper(TYPE)MOBILE
• NO INDEX is USED!!!

10
Global Rules

• Every table has a Primary Key Field called ID
  Integer and AUTOINC
• InterBase user Generator and Trigger
• JDataStore AutoInc Property
• This Column has no meaning. This is only for the
  internal structure and very quick joins over
  integer relations

11
Global Rules

• use a global name syntax
• Tables TBL_
• Index Primary INDPK_
• Other Index IND_
• Domains DOM_
• Generators GEN_
• Stored Procedures SP_
• select from CUSTOMER -gt what ist CUSTOMER ?
• select from TBL_CUSTOMER
• select from SP_CUSTOMER

12
Global Rules

• JDataStore
• New in Version 7 SCHEMAS
• The following statement creates the schema BORIS
  with a table T1 and a view V1. In this schema,
  the user BJORN is granted SELECT privileges on
  view V1. Afterthis statement executes, BORIS is
  the default schema for user BORIS.
• USER BORIS
• CREATE SCHEMA BORIS
• CREATE TABLE T1 (C1 INT, C2 VARCHAR)
• CREATE VIEW V1 AS SELECT C2 FROM T1
• GRANT SELECT ON V1 TO BJORN
• See for details the JDataStore Developers Guide

13
Step 2 Fieldnames

• User Fieldnames with English Names
• KUNDENNUMMER CUSTNO
• It is much better for future
• International developers
• Bring your software world wide

14
Step 2 Fieldnames

• Use only UPPERCASE
• select CUSTNO from
• is not the same as
• Select custno from

15
Step 2 Fieldnames

• Use Fieldname syntax rules
• Link Fields to other tables have the source
  tablename in the fieldnameltID_COMMUNICATIONgtever
  ybody knows the source
• this is the ID from the table communication

16
Step 2 Fieldnames

• Use Fieldname syntax rules
• For lookuptables use the same Fieldnames if
  possible

Property keyfield
Property display value
Benefit for Develops Objects wich incl. this
logic
17
Step 2 Fieldnames

• Use Fieldname syntax rules
• Link Fields to other tables have the source
  tablename in the fieldnameltID_COMMUNICATIONgtever
  ybody knows the source
• this is the ID from the table communication

18
Step 3 Fieldtypes

• InterBase
• I use only domains. If not InterBase create for
  each field a internal domain (RDBDOMAINS)
• Changing the Articlenr from INT to INT64 is so
  easy now in one step incl. all other fields which
  a child from this field.

19
Step 3 Fieldtypes

• Invest 1 hour in the fieldtype documentation, do
  you know all about the size and the Precision?
• InterBase
• DOUBLE PRECSION 64 bits
• FLOAT 32 bits
• Think about number of records

20
Step 3 Fieldtypes

• JDataStore

21
Step 3 Fieldtype

• String Fieldtypes
• InterBase Collation Orders
• JDataStore Local property
• Example

22
Step 4 Index

• Primary Key
• Each Table have a primary key! Do not create a
  table without a primary key!!!!
• ID Field are always the primary key later the
  CustNo is a unique index
• Do not create a primary key on a string field
  alone ! Performance killer

23
Step 4 Index

• How a Index work
• ASC index is not a DESC Index
• Example

24
Step 4 Index

• How a Index work
• InterBase Index are case sensitive
• JDataStore Index can be case or not case
  sensitive
• Example

25
Step 4 Index

• How a Index work
• only(!) JDataStore can create Index with Local
  attributes!!!! (GREAT!)
• Example

26
Step 4 Index

• Index
• A Index is a Prefix Index. That is also a reason
  why a ASC Index is not a DESC index. How a prefix
  Index work

27
Step 4 Index

• Simple Example

28
Step 4 Index

• Create index IND_EX1 on TBL_CUSTOMER (CUST_NO,
  LASTNAME, FIRST_NAME)
• Select from TBL_CUSTOMER where CUST_NO 1
  IND_EX1 WORK
• Select from TBL_CUSTOMER where CUSTO_NO gt100
  and LAST_NAME Miller IND_EX1 WORK
• Select from TBL_CUSTOMER where LAST_NAME
  Miller IND_EX1 NOT WORK

29
Step 4 Index

• Create index IND_EX1 on TBL_CUSTOMER (CUST_NO,
  LASTNAME, FIRST_NAME)
• Select from TBL_CUSTOMER where LAST_NAME
  Miller and LAST_NAME Mike IND_EX1 NOT
  WORK

30
Step 4 Index

• Create unique index IND_EX1 on TBL_CUSTOMER
  (CUST_NO)
• Create index IND_EX2 on TBL_CUSTOMER (LAST_NAME,
  FIRST_NAME)
• Select from TBL_CUSTOMER where CUST_NO 1
  IND_EX1 WORK
• Select from TBL_CUSTOMER where CUSTO_NO gt100
  and LAST_NAME Miller IND_EX1 and IND_EX2
  Merge -gt WORK
• Select from TBL_CUSTOMER where LAST_NAME
  Miller IND_EX2 WORK

31
Step 4 Index

• Create unique index IND_EX1 on TBL_CUSTOMER
  (CUST_NO)
• Create index IND_EX2 on TBL_CUSTOMER (LAST_NAME,
  FIRST_NAME)
• Select from TBL_CUSTOMER where LAST_NAME
  Miller and FIRST_NAME Mike IND_EX2 WORK
• Select from TBL_CUSTOMER where FIRST_NAME
  Mike NO IND WORK

32
Step 4 Index

• Create index IND_EX1 on TBL_CUSTOMER (LAST_NAME)
• Select from TBL_CUSTOMER where LAST_NAME
  Miller IND work
• Select from TBL_CUSTOMER where LAST_NAME like
  Mi IND work
• Select from TBL_CUSTOMER where LAST_NAME like
  il IND not work

33
Step 4 Index

• Each Index will slow down your insert, update and
  delete statements but speed up your select
  statements

34
Step 4 Index and Blobs or Text Objects

• To find quick text in a BLOB field you can use
  this simple trick
• Build your own Index Tables

35
Step 4 Index and Blobs or Text Objects
36
Step 5 Foreign Keys

• Most DataBase Designer dont use Foreign Key
  Structures. Why?
• I dont know.

37
Step 5 Foreign Keys

• With Foregin Key you can hold very simple your
  content in the database clean!
• Samples
• Nobody can delete a customer with bills
• If you delete a customer all depending records
  are also delteted
• If you delete a customer all the the dependig
  records are set the customer number to NULL
• If you change the customer number all tables with
  this customer number are also changed

38
Step 5 Foreign Keys

• CREATE GENERATOR GEN_TBL_CUSTOMER_ID
• CREATE TABLE TBL_CUSTOMER (
• ID DOM_ID NOT NULL,
• CUSTNO DOM_CUSTID NOT NULL,
• NAME DOM_CUSTNAME)
• ALTER TABLE TBL_CUSTOMER ADD CONSTRAINT
  PK_TBL_CUSTOMER PRIMARY KEY (ID)
• CREATE TABLE TBL_CUSTOMERINFO (
• ID DOM_ID NOT NULL,
• ID_TBL_CUSTOMER DOM_ID NOT NULL,
• SVALUE VARCHAR(100)
• )
• ALTER TABLE TBL_CUSTOMERINFO ADD CONSTRAINT
  PK_TBL_CUSTOMERINFO PRIMARY KEY (ID)

39
Step 5 Foreign Keys

• Result
• USE IT!
• I found in 70 from customer databases no FK

40
Step 6 Table Technics

• Storing Trees or never ending structures in one
  table (loop technic)
• History Lotus Notes use a simple technic to save
  in one table all records with dependencies
• Each Record have a ID
• Each Record can have a Parent ID

41
Step 6 Table Technics
42
Step 6 Table Technics

• InterBase case insensitv index

CREATE TRIGGER TBL_TEXTE_BU0 FOR TBL_TEXTE ACTIVE
BEFORE UPDATE POSITION 0 AS begin
new.UPPERSVALUE UPPER(new.SVALUE) End Select
SVALUE from TBL_SOMETHING where UPPERSVALUE
MILLER RESULT Miller, MiLLer, MILLER,
Remember JDataStore have case insensitive index
technique
43
Step 7 Read and write only over StoredProcedures

• User can only read and write over Stored
  Procedures. Only the admin/sysdba have the rights
  to read/write in tables
• Benefits
• You can split tables and change the SP but nobody
  have to change the code
• Perfect control of statements
• Including special search algorithms in the SP
• Easier handling of grants
• SP_TBL_SOMETHING_SELECT
• SP_TBL_SOMETHING_UPDATE
• SP_TBL_SOMETHING_DELETE
• SP_TBL_SOMETHING_INSERT

44
Step 8 Replication

• Replication is a difficult mechanism for DataBase
  Designer
• Problems
• Generators / AutoInc
• Unique Fields like CustomerNr

45
Step 8 Replication

• Generators/AutoInc
• Problem
• Each server have a own counter
• Solutions
• Server 1 starts at 0
• Server 2 starts at 100.000
• Server 3 starts at 200.000
• But this is not a endless solution

46
Step 8 Replication

• Replication is a difficult mechanism for DataBase
  Designer
• Problems
• Generators / AutoInc
• Unique Fields like CustomerNr

47
Step 8 Replication

• Generators / Autoinc
• Most Newsgroups solution
• Use ID not Integer use GUID
• 91701993-5318-4467-83AC-053AD2EA3EBC
• This is now a string (performance)

48
UDF example in Delphi for InterBase

• uses SysUtils,ib_util
• function IBCreateGUID PChar cdecl
• var
• T String
• TMP Pointer
• i integer
• GUID TGUID
• sPChar
• begin
• CreateGUID(GUID)
• tGUIDToString(GUID)
• tmpib_util_malloc(38)
• tmpPChar(t)
• resultTMP
• //Char 38
• end

Delphi Code
49
Step 8 Replication

• Enhance String Performance
• Include in the PK a second Field REPID from
  string 38 for GUIDPK ID REPID
• Dont use REPID alone as PK, it is a string -gt
  slows down your select
• Use a UDF for create GUIDs in the Database by a
  trigger

50
Step 8 Replication

• My personal solution
• SNServer count
• IDGENIDSNServerNumber
• Exampe you have 256 Servers to Replicate
• IDGENID2561 for Server 1
• IDGENID2562 for Server 2
• IDGENID256n for Server n 1ltnlt256

51
Step 8 Replication
52
Step 8 Replication

• Integer 32bit
• Each table can have 16.777.216 Records by 256
  Servers
• On each table with max 1.000.000 Records you can
  replicate 4294 servers
• Integer 64bit
• Each table can have 72.057.594.037.927.936
  Records by 256 Servers
• On each table with max 1.000.000 Records you can
  replicate 18.446.744.073.709 servers

53
Questions?
54
Thank You

• 3118
• InterBase and JDataStore Database Design
• Please fill out the speaker evaluation
• You can contact me further at dmagin_at_better-offi
  ce.com,
• dmagin_at_borland.com