This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

--Using master/detail relationships

Documents for the development of and with JVx.

--Using master/detail relationships

Postby Development@SIB » Wed Sep 15, 2010 6:43 pm



This article is outdated - please use our new system at

https://doc.sibvisions.com




Master/detail relationships are used to create relationships between tables or other datasets. The master can also be viewed as a group, whereas the detail contains the individual sets of the group.

In short, master/detail is used to show the 1:n and n:m relationships of a database.


Example

Our application handles the administration of countries, states and districts. Tables in the database are created in 3rd NF. The following is an excerpt from the HSQLDB:

Code: Select all
create table COUNTRIES
(
  ID      INTEGER IDENTITY,
  COUNTRY VARCHAR(200) not null,
  EU      CHAR(1) default 'N' not null,
  constraint CTRY_UK unique (COUNTRY)
)

create table STATES
(
  ID      INTEGER IDENTITY,
  CTRY_ID INTEGER not null,
  STATE   VARCHAR(200) not null,
  constraint STAT_UK unique (STATE),
  constraint STAT_CTRY_ID_FK foreign key (CTRY_ID) references COUNTRIES (ID)
)

create table DISTRICTS
(
  ID       INTEGER IDENTITY,
  STAT_ID  INTEGER not null,
  DISTRICT VARCHAR(200),
  constraint DIST_UK unique (DISTRICT),
  constraint DIST_STAT_ID_FK foreign key (STAT_ID) references STATES (ID)
)

As the table definition shows, countries consist of individual states and states are made up of districts. In our application we want to create a form that allows the editing of countries, states and districts, as well as the relationships between them.

We first create our server objects:

Code: Select all
/**
 * Returns the countries storage.
 *
 * @return the Countries storage
 * @throws Exception if the initialization throws an error
 */
public DBStorage getCountries() throws Exception
{
   DBStorage dbsCountries = (DBStorage)get("countries");
      
   if (dbsCountries == null)
   {
      dbsCountries = new DBStorage();
      dbsCountries.setDBAccess(getDBAccess());
      dbsCountries.setFromClause("V_COUNTRIES");
      dbsCountries.setWritebackTable("COUNTRIES");
      dbsCountries.open();
      
      put("countries", dbsCountries);
   }
      
   return dbsCountries;
}
   
/**
 * Returns the districts storage.
 *
 * @return the Districts storage
 * @throws Exception if the initialization throws an error
 */
public DBStorage getDistricts() throws Exception
{
   DBStorage dbsDistricts = (DBStorage)get("districts");
      
   if (dbsDistricts == null)
   {
      dbsDistricts = new DBStorage();
      dbsDistricts.setDBAccess(getDBAccess());
      dbsDistricts.setFromClause("V_DISTRICTS");
      dbsDistricts.setWritebackTable("DISTRICTS");
      dbsDistricts.open();
      
      put("districts", dbsDistricts);
   }
      
   return dbsDistricts;
}
   
/**
 * Returns the states storage.
 *
 * @return the States storage
 * @throws Exception if the initialization throws an error
 */
public DBStorage getStates() throws Exception
{
   DBStorage dbsCountries = (DBStorage)get("states");
   
   if (dbsCountries == null)
   {
      dbsCountries = new DBStorage();
      dbsCountries.setDBAccess(getDBAccess());
      dbsCountries.setFromClause("V_STATES");
      dbsCountries.setWritebackTable("STATES");
      dbsCountries.open();
      
      put("states", dbsCountries);
   }
   
   return dbsCountries;
}

The view definition for processing the data:

Code: Select all
CREATE VIEW V_COUNTRIES AS
select c.ID
      ,c.COUNTRY
      ,c.EU
  from COUNTRIES c
 order by c.COUNTRY

CREATE VIEW V_STATES AS
select s.ID
      ,s.CTRY_ID
      ,s.STATE
  from STATES s
 order by s.STATE
 
CREATE VIEW V_DISTRICTS AS
select d.ID
      ,d.STAT_ID
      ,d.DISTRICT
  from DISTRICTS d
 order by d.DISTRICT


We now have to create the connection to the server objects and define the master/detail relationships:

Code: Select all
rdbCountries.setDataSource(dataSource);
rdbCountries.setName("countries");
rdbCountries.open();
      
rdbStates.setDataSource(dataSource);
rdbStates.setName("states");
rdbStates.setMasterReference(new ReferenceDefinition(new String[] {"CTRY_ID"},
                                 rdbCountries, new String[] {"ID"}));
rdbStates.open();

rdbDistricts.setDataSource(dataSource);
rdbDistricts.setName("districts");
rdbDistricts.setMasterReference(new ReferenceDefinition(new String[] {"STAT_ID"},
                                    rdbStates, new String[] {"ID"}));
rdbDistricts.open();

A master/detail relationship can be created as follows:

Code: Select all
rdbDistricts.setMasterReference(new ReferenceDefinition(new String[] {"STAT_ID"},
                                    rdbStates, new String[] {"ID"}));

hergestellt.

The master/detail relationship between countries and states is created via the foreign key (STATES.CTRY_ID) to the primary key (COUNTRIES.ID). This means that when a country is selected, all associated states are displayed. States that are not assigned to the selected “master” country are not shown.

When a new state is entered, the correct foreign key is used automatically. The state is therefore assigned to the correct country.

The master/detail relationship between districts and states is created between the foreign key (DISTRICTS.STAT_ID) and the primary key (STATES.ID). This means that when a state is selected, all associated districts are displayed. Districts that are not assigned to the selected “master” state are not shown.

When a new district is entered, the correct state is used. The district is therefore automatically assigned to the correct state.


Note
A master/detail relationship does NICHT require that a foreign key relationship exists between two tables. Any columns can be used.
User avatar
Development@SIB
 
Posts: 325
Joined: Mon Sep 28, 2009 1:54 pm

Return to Documentation