Designing a Database

1. Purpose. One or two sentences. Riverside Public Library needs to remember who has joined, which titles it owns, who borrowed what and when, and which member has which locker. If a fact does not serve that purpose, it does not need a column.

2. Gather the facts. Membership forms, the book list, the loan register, the locker plan. Names, cities, phones, titles, authors, dates, locker locations.

3. Entities — kinds of thing. Group the facts. People who join are one kind. Titles on the shelf are another. A borrow is a third. A locker is a fourth. Each kind will become a table.

4. Fields — attributes. For each kind, list the facts you store about it. A member has a name, a city, a phone. On paper these are attributes. In the finished table they are fields.

5. Primary keys. Each table needs a stable identity: MemberID, BookID, LoanID, LockerID. Test names and phones; they fail.

6. Relationships. Where a fact on one list must match a fact on another, draw the line on purpose: Loans.MemberID to Members.MemberID, and so on. Decide one-to-one, one-to-many, or many-to-many. A many-to-many needs a table in the middle.

7. Sample rows. Look at real examples. Two Sophie Walshes. The same book borrowed twice. A locker that must not be shared. Fix the design before the data grows.

8. Normalisation check. One value per box. No leftover phones on loan rows. No fine hanging off a type that should have its own list. If a check fails, split a table and return to step 3.