Publications
Data Modeling Essentials
Academic Research
Education & Training
Home


Roy's Antiques
Pinot Now
Our House in France
Family History/Genealogy

 

What goes Where? Perceptions of Stages in the Data Modelling Process
In November 2004, I gave seminar / Master Class attendees in London and Los Angeles a questionnaire which asked them to nominate the main stages in database specification (from user need to completed specification). I allowed room for five (a couple squeezed in six). I got 55 responses (25 from London , 30 from LA). I also asked respondents to whether each stage was “data modelling” and who was responsible.

What are the perceived stages?
The table below shows the range of data modelling stages nominated by survey respondents (there has been some consolidation to deal with minor variations in names, and anything that happened after the initial specification – e.g. ‘testing' – was classified as “Post Design Activities”). They are sorted in order of “average stage number” – to give an overall idea of where they appeared in sequence.

Standard Stage Code Name Number of respondents (total 55) who included this stage Percentage who classified the stage as data modelling

Preliminary

5

60.0%

Business Requirements

46

43.5%

Process Analysis

3

0.0%

Conceptual Modelling

32

84.4%

Architecture Activities

4

75.0%

Analysis

2

50.0%

Technical Requirements

1

0.0%

Data Modelling

9

100.0%

Logical Data Modelling

41

100.0%

Design

2

50.0%

Other

8

62.5%

Logical & Physical Data Modelling

3

100.0%

First Cut Physical

2

100.0%

Physical Data Modelling

33

69.7%

Validation

3

100.0%

Physical Database Design

22

40.9%

Post Design Activities

27

22.2%

How do the stages fit together?
I classified the responses into a series of patterns shown in the table below. The decimal numbering reflects the fact that detailed analysis of activities (see below) showed that respondents were using the terms “physical data modelling” and “physical database design” either as synonyms or (in only 6 cases) as a way of splitting that stage into two parts.

Pattern Number

Count LA

Count London

Total

Stage 1

Stage 2

Stage 3

Stage 4

Stage 5

1.0

6

5

11

Business Requirements

Conceptual DM

Logical DM

Physical DM

1.1

2

2

4

Business Requirements

Conceptual DM

Logical DM

Physical DBD

1.2

2

2

4

Business Requirements

Conceptual DM

Logical DM

Physical Modelling

Physical DB Design

2.0

7

0

7

Conceptual DM

Logical DM

Physical DM

2.1

0

1

1

Conceptual DM

Logical DM

Physical DBD

3.0

4

2

6

Business Requirements

Data Modelling

Physical DB Design

4.0

2

1

3

Business Requirements

Conceptual DM

Logical/Physical

5.0

1

0

1

Business Requirements

Conceptual/ Logical DM

Physical DM

6.0

3

2

5

Business Requirements

Logical DM

Physical DM

6.1

1

5

6

Business Requirements

Logical DM

Physical DBD

6.2

0

1

1

Business Requirements

Logical DM

Physical DM

Physical DBD

7

0

1

1

Conceptual/ Logical DM

Physical DM

Other

2

3

5

TOTAL

30

25

55

Who is responsible for each stage?
The table and graph below show the most commonly cited job responsibilities for the most commonly-cited stages.

Standard Stage Code Name

Avg Of Stage No (sequence indicator)

Data Modeller

DBA

Analyst

User

Business Requirements

1.15

46%

7%

65%

39%

Conceptual Modelling

1.91

88%

6%

19%

16%

Logical Data Modelling

2.80

93%

10%

17%

7%

Physical Data Modelling

3.91

76%

67%

3%

0%

Physical Database Design

4.09

32%

82%

9%

0%

Post Design Activities

4.52

37%

59%

15%

11%

Who is responsible? The “Avg of DM” line shows the percentage of respondents nominating this stage who answered “yes” to the question “Is it data modelling?”

What activities get done in what stages?
The questionnaire listed 26 ‘activities' and asked respondents to nominate the stage to which they belonged. The table below shows the activities sorted by the stage number to which they were allocated (giving an idea of overall sequence). It also shows the answers to “is it data modelling?” and “who is responsible?” for the stage to which the activity was allocated.

Sequence

Activity Name

Is it Data Modelling?

DM Resp.

DBA

Resp.

Analyst Resp.

User

Resp.

1

Identify business (data) requirements

45.2%

45.2%

2.4%

61.9%

28.6%

2

Negotiating compromises with the business stakeholders

76.3%

71.1%

18.4%

34.2%

10.5%

3

Identify entities and relationships

90.7%

81.4%

7.0%

27.9%

9.3%

4

Documenting rules about data which cannot be represented in the data model

82.5%

82.5%

10.0%

22.5%

10.0%

5

Ensuring that business change can be accommodated

81.1%

83.8%

2.7%

21.6%

16.2%

6

Ensuring (user) data requirements are met

78.9%

73.7%

15.8%

21.1%

10.5%

7

Identify attributes

95.7%

89.1%

10.9%

17.4%

6.5%

8

Specifying attribute domains

88.9%

84.4%

15.6%

11.1%

8.9%

9

Normalization

93.9%

85.7%

16.3%

18.4%

4.1%

10

Integrating different views of the data

82.5%

82.5%

22.5%

7.5%

7.5%

11

Specifying primary keys

95.7%

87.0%

17.4%

10.9%

2.2%

12

Translating an E-R (or other conceptual) model into a relational model

96.0%

86.0%

24.0%

14.0%

6.0%

13

Disaggregating attributes into atomic data

88.9%

80.0%

17.8%

6.7%

6.7%

14

Including foreign keys

91.7%

85.4%

20.8%

14.6%

2.1%

15

Resolving many-to-many relationships

100%

89.1%

15.2%

13.0%

2.2%

16

Removing derivable data

95.6%

91.1%

26.7%

15.6%

2.2%

Changing the database to meet new business requirements

74.0%

69.0%

26.0%

0.0%

26.0%

17

Specifying the datatype for each column

85.4%

83.3%

31.3%

6.3%

4.2%

18

Specifying column names

85.1%

83.0%

42.6%

4.3%

2.1%

19

Specifying how subtype hierarchies will be implemented

83.0%

80.9%

44.7%

4.3%

2.1%

Advising programmers on understanding the database

73.0%

73.0%

54.0%

0.0%

8.0%

20

Specifying views

65.8%

57.9%

57.9%

13.2%

5.3%

21

De-normalization for performance

62.5%

64.6%

62.5%

8.3%

2.1%

22

Specifying data distribution

41.2%

38.2%

76.5%

5.9%

0.0%

23

Adding derivable data to improve performance

63.6%

63.6%

63.6%

9.1%

4.5%

24

Specifying database indexes

48.8%

48.8%

72.1%

7.0%

0.0%

25

Specifying physical placement of data on storage media

47.1%

51.0%

68.6%

7.8%

2.0%

26

Tuning the database for performance

38.0%

42.0%

70.0%

12.0%

6.0%

(Two sequence numbers are missing – these activities were excluded from later analysis (and the graph below) as they did not represent design decisions within the scope of the question.

As always, just an overview of results. A more complete version is on its way (with appropriate academic rigour) to my PhD thesis, and will doubtless also be published in a practitioner-friendly form at some stage.

Summary of Surveys, Interviews and Experiments

What is Data Modelling? (Survey Results) posted 2nd December 2004

Data Modeling - Analysis or Design? (Survey results and comparison with Accountants & Architects) posted 17th December 2004