Develop your skills - Relationships
Skills - designing databases
The design of tables and relationships is a crucial step in establishing a relational database.
A poor table design
is likely to result in a database with serious problems. The next two readings explain some of the problems that can occur with bad table design.
As you read, concentrate on recognising when and how these problems occur.
Designing tables succesfully includes a process called normalisation, which is beyond the scope of this course. For small databases, recognising the problems is probably sufficient. If you wish to design larger databases you will need to learn more about this area.
SET TEXT
Chapter 4
pp. 399-411
or
Chapter 4
pp. 397-409
- how to create 1 to many relationships
- when and how to set Referential Integrity and
- when and how to set the Cascade Delete and Cascade Update options.
TASK 1:
- Complete the Develop Your Skills 7, p. 409. This exercise asks you to Enforce Referential Integrity and the Cascade Delete options in parts 6 and 7 and then test them in parts 10 and 11.
- Complete the Develop You Skills 8, p. 411. This exercise introduces you to the Table Analyze feature which may help you to understand table organisation better.
TASK 2:
On p. 404, your textbook writes "many-to-many (where many customers
are involved with many videos)... should never be seen".
- Using the examples of customers and videos, explain why many-to-many should never be seen?
- How do you avoid many-to-many relationships in your database?
When designing a database it is important not to have unnecessary tables (complicates the database) but likewise it is also critical not to have too few tables (possibly many-to-many relationships). In general, each table should represent one entity or object, e.g. customers, videos or rentals. Designing the database tables is a difficult process but you should spend time trying to get it "right". If your table design has problems you are likely to find difficulties in implementing and using your database. Almost without exception a bad design will lead to a bad database.
Never duplicate data in a database. Most of the problems highlighted in this topic arise from duplication of data.
What have I learned?
After completing these activities, you should:- understand the crucial importance of designing databases well and recognise problems that occur in badly designed databases
- be able to identify 1:N and N:M relationships
- be able to establish relationships on Access.