- Foundation
- About 13 min
- 5 practice questions
Designing a simple database step by step
Plan a small database from scratch: find entities, choose fields and keys, link tables, and decide on forms, queries and reports.
If something looks wrong, please tell us.
Good databases begin on paper. Before anyone opens software, the designer asks what things must be recorded and how they are connected. A small school library is a perfect example because everyone understands it.
Step 1: Find the entities
An entity is a thing about which we store data. For a library: BOOK, MEMBER and the act of borrowing, which we can call LOAN. Each entity will become a table.
Step 2: List the attributes (fields)
- BOOK: BookID, Title, Author, Subject.
- MEMBER: MemberID, Name, Class, Phone.
- LOAN: LoanID, BookID, MemberID, IssueDate, ReturnDate.
Step 3: Choose primary keys
Each table needs a field that identifies each row uniquely: BookID, MemberID and LoanID. Never use Name as a key, because two members can share a name.
Step 4: Link the tables with foreign keys
LOAN contains BookID and MemberID. These are foreign keys: they point to the primary keys in BOOK and MEMBER. One member can borrow many books over time, and one book can be borrowed many times, so BOOK and MEMBER have a many-to-many relationship. The LOAN table sits in between and splits it into two one-to-many relationships.
Step 5: Add data types and rules
- IDs: integer or AutoNumber.
- Names, titles: text. Phone: text.
- IssueDate, ReturnDate: date. ReturnDate may be NULL while the book is out.
- Rule: ReturnDate must not be earlier than IssueDate.
Step 6: Decide how people will use it
- Forms let staff enter data one record at a time with a friendly screen.
- Queries ask questions such as 'Which books are not yet returned?'.
- Reports present results for printing, such as a list of overdue books.
Worked examples
Example 1. Why not put the member's name and class inside every LOAN row instead of making a MEMBER table? If a member borrows 50 books, her name and class would be stored 50 times. When her class changes, all 50 rows need updating, and missing one makes the data inconsistent. With a MEMBER table, the details are stored once and changed once.
Example 2. In LOAN, which fields are foreign keys and which is the primary key? LoanID is the primary key. BookID and MemberID are foreign keys, because each must match an existing value in BOOK or MEMBER. If someone enters BookID 999 and no such book exists, the database refuses it. This is referential integrity.
Example 3. Suppose the sample data is: MEMBER (101, Neha), (102, Imran). LOAN rows: (1, book 5, member 101), (2, book 7, member 101), (3, book 5, member 102). How many loans has Neha made, and how many members have borrowed book 5? Neha (101) appears in 2 loans. Book 5 appears in loans 1 and 3, by members 101 and 102, so 2 members have borrowed it.
Example 4. Which tool answers 'List all members who have not returned a book'? A query on LOAN where ReturnDate is NULL, joined to MEMBER for the names. The printed list of the result would be a report.
Entities become tables, identifiers become primary keys, and links become foreign keys.
Key takeaways
- Design on paper first: entities, attributes, keys, links.
- Store each fact once to avoid inconsistency.
- A linking table turns many-to-many into two one-to-many relationships.
- Forms enter data, queries ask questions, reports present answers.
Check your understanding
-
1. In a library database, BOOK and MEMBER are examples of
- Reports
- Entities
- Forms
- Queries
Show answer
Answer: B. Things about which data is stored are entities, and each normally becomes a table.
-
2. In the LOAN table, BookID is a
- Report
- Default value
- Foreign key
- Query
Show answer
Answer: C. BookID in LOAN points to BookID in the BOOK table, so it is a foreign key.
-
3. Why is a MEMBER table better than repeating member details in every loan row?
- It makes the database much bigger in size
- Details sit in one place and stay consistent
- It removes every key from the database
- It stops any queries from being run
Show answer
Answer: B. Storing details once avoids repetition and update mistakes.
-
4. A printed list of overdue books is best produced as a
- Form
- Key
- Table
- Report
Show answer
Answer: D. A report presents query results in a printable layout.
-
5. Which field is the most suitable primary key for MEMBER?
- MemberID
- Name
- Class
- Phone
Show answer
Answer: A. An ID is unique to each member. Names and classes can repeat.
Sign in to keep track of the lessons you finish.