Chapter 1: Design database system

1.1 Identification of Database design approaches

A database design approach is the strategy a designer follows to move from a business problem to a finished database design: where to start, in what order to work and who does the work. Choosing the approach before anything is drawn matters because it decides how requirements are gathered, how the tables are discovered and how much rework is needed later. Example: a courier company that wants to track parcels may begin from the questions its managers ask, such as "where is this parcel now?", or from the paper waybill its clerks already fill in. Those two starting points lead to two different approaches.

Four approaches are identified in this topic. Two describe the direction of the work (top-down and bottom-up) and two describe how the work is organised across people and sites (centralised and decentralised). A real project often combines one from each pair, for example a centralised team that works top-down.

1.1.1 Design approaches

A design approach answers three practical questions: what is the starting point, who does the design and how the pieces are put together. The tools, models, normalisation rules and diagrams met in the rest of this chapter are the same whichever approach is chosen; only the order in which they are used changes.

1.1.1.1 Top – down design method

The top-down method starts with a broad view of the organisation and refines it step by step. The designer first identifies the main things the business keeps information about (entities), then the relationships between them, and only afterwards fills in the attributes, keys and finally the tables. It follows the path from conceptual design, to logical design, to physical design.

Example: for a car hire company the designer starts from the business sentence "customers book cars for a period". That gives the entities Customer, Car and Booking and the relationships between them, long before anyone decides on column names or data types.

Steps in the top-down method:

1. Define the purpose, scope and users of the system by interviewing managers and staff.
2. List the business rules and the questions the database must answer.
3. Identify the entities and the relationships between them.
4. Draw the conceptual model as an entity relationship diagram.
5. Add the attributes of each entity and choose a key for each.
6. Convert the diagram into tables, which is the logical design.
7. Normalise the tables, then choose data types, indexes and storage, which is the physical design.

Strengths and limits of the method:

• Complete view: The whole organisation is considered, so no major entity is forgotten.
• Early agreement: Managers can check a simple diagram before any technical work begins.
• Less rework: Tables come from a reviewed model, so large changes late in the project are rare.
• Needs good analysis: It depends on analysts who understand the business well enough to name the entities correctly.
• Slow to show results: Users see no working screens until the later stages.

1.1.1.2 Bottom – up design method

The bottom-up method starts with the smallest pieces of data, the attributes, and builds upwards. The designer collects every data item from existing forms, reports, spreadsheets and files, finds out which items depend on which, and groups items that belong together into tables. The grouping is done with the normalisation rules, and the tables are then linked with keys.

Example: a hardware shop keeps its stock in a spreadsheet with columns such as item code, item name, supplier name, supplier phone, buying price and quantity. A bottom-up designer studies these columns and notices that supplier name and supplier phone depend on the supplier, not on the item, so a separate Supplier table is formed.

Steps in the bottom-up method:

1. Collect every form, report, register and spreadsheet used in the current system.
2. List all the data items (attributes) that appear in them and remove duplicates.
3. Record which items determine other items, for example item code determines item name.
4. Group items with the same determinant into candidate tables.
5. Choose a primary key for each table and link the tables with foreign keys.
6. Check that every form and report can be produced from the new tables.
7. Review the result with users and correct missing or misplaced attributes.

Strengths and limits of the method:

• Quick start: Real documents give concrete data from the first day.
• Suits small systems: It works well when a paper or spreadsheet system is being replaced by a database.
• Accurate attributes: Every attribute comes from something users already record.
• Risk of gaps: Entities that are not visible on any current form can be missed.
• Risk of confusion: In a large organisation the number of data items becomes too big to group by hand without an overall plan.

1.1.1.3 Centralised design

In centralised design one designer or one design team collects the requirements of the whole organisation and produces a single database design. All decisions about names, data types, keys and rules are taken in one place, so the result is consistent from the start.

Example: a small tailoring business with one shop and four staff asks one developer to design its database. The developer interviews the owner, the cutters and the person who handles payments, and produces one design covering customers, orders, measurements and payments.

Features of centralised design:

• One authority: A single person or team owns the design and settles disagreements.
• Common standards: One naming convention and one set of data types is used throughout.
• Single conceptual schema: There is nothing to merge, so there are no conflicts between designs.
• Easy control: Changes to the design are approved in one place and recorded in one design report.
• Limits: The designer may not understand every department, the work can become a bottleneck, and the method is slow when the organisation is large.

1.1.1.4 Decentralised design

In decentralised design each department, branch or project team designs the part of the database it understands best. The local designs are then compared and merged into one global design. This merging is called view integration.

Example: a courier company has a branch office team that designs tables for parcel intake, a depot team that designs tables for sorting and loading, and a delivery team that designs tables for riders and routes. All three designs contain a Parcel entity, and the integration step decides what the single Parcel table will contain.

Steps in decentralised design:

1. Divide the organisation into areas and give each area to a team.
2. Each team gathers its own requirements and designs a local schema.
3. Compare the local schemas and list the conflicts between them.
4. Resolve naming conflicts: synonyms (two names for the same thing, such as Client and Customer) and homonyms (one name for two different things, such as Route meaning a delivery road in one team and a network route in another).
5. Resolve type and structure conflicts, for example one team storing a phone number as text and another as a number.
6. Merge the local schemas into one global conceptual schema and remove duplicated entities and attributes.
7. Check the global schema with every team and approve it.

Advantages and limits:

• Local knowledge: Each team designs the part it knows best.
• Parallel work: Teams work at the same time, which shortens the project.
• Integration effort: Merging needs careful comparison and can be difficult if teams used different names and types.
• Risk of duplication: The same fact may be stored in several local designs until integration removes it.
• Not the same as distributed storage: Decentralised design describes how people share the design work. Where the data is stored is a different question, answered by the Distributed DBMS architecture.

Choosing between the approaches

The approaches are chosen to fit the project, not the other way round. A small organisation with one site and a short timetable can work centralised, and either top-down or bottom-up depending on whether documents already exist. A large organisation with several branches may work decentralised, and needs a top-down overall plan so that the local designs can be merged. Many designers also mix directions: they model the core entities top-down, then check the model bottom-up by confirming that every existing form and report can be produced from it.