Relationship and key-design workbench

This workbench checks the keys in a small fictional sample that you type, and shows how a junction table turns a many-to-many relationship into two one-to-many links. It is for students who can name a primary key but cannot yet defend their choice or find a broken link.

How do I use it?

1. Name the two entities and the junction table. The defaults are Students, Clubs and Membership.
2. Type one row per line in each entity table as a key and a label, for example S1, Aina. Type one pair per line in the junction table, for example S1, C1.
3. Choose the relationship you believe exists.
4. Press “Check my design”, then write your own reasoning in the rationale box.

How does it work?

For each entity table the tool checks that the key is present in every row and appears only once. For the junction table it checks that each value matches a real key in the table it points to, and that no pair repeats.

A missing key, a duplicate key, an orphan foreign key and a repeated pair are each reported with the row numbers. A repeated label is also flagged, because a name that can repeat cannot be a key.

If you declare one-to-many or one-to-one, the tool compares that with the links in your sample. It says when the sample behaves as many-to-many instead.

A short worked example

Students has keys S1 and S2, Clubs has C1 and C2, and Membership holds the pairs (S1, C1), (S1, C2) and (S2, C1). All three tables pass, and each pair together works as the key of Membership.

Now add (S3, C1). Student S3 does not exist, so the tool reports an orphan foreign key on that row.

Instead repeat (S1, C1). The pair now appears twice, so the tool reports a duplicate pair and explains that a composite primary key must be unique.

What are its limits?

The tool works on the fictional rows you type, with at most 40 rows per table. It supports your reasoning and does not complete a project, design a whole database or guarantee a fully normalised design.

Do not type real names, IC numbers or contact details. Nothing is sent or saved, and your rationale stays on this page until you leave it.

Where does it connect to the lessons?

The workbench supports choosing primary and foreign keys, reading an entity-relationship diagram and distinguishing entities, attributes and relationships. The wider map is in relational data modelling.

Two focused pages cover choosing a primary key when names are not unique and resolving a many-to-many relationship with an intermediate table.

If the reason behind a design choice is the weak spot, SPM Computer Science tuition gives you a teacher to test it with you. It starts with the one-hour trial class (from RM50).