Entities

[cuttsy]

Can people give their thoughts on the following sernario.

When I have been taught database design in the past it has always been with the aid of examples that have very distinct entities. For example: People loan copies of books.

For the sake of simplicity say you have a booking system.

Event(EventID, EventName, EventDate)
Person(PersonID, Firstname, Surname, ... Postcode)
Booking(BookingID, Person, Event, Guests)

This is the same level of simplicity. However I have never been shown an example where the person entity is more specialised.

What if a database needs to hold details on people of different types? Say you hold college students, school techers and business owners. The details that is required on each is:

Student(Firstname, Surname, College, Address, DOB)
Teacher(Firstname, Surname, School, JobTitle)
BusinessOwner(Firstname, Surname, Business)

I assume that this requires three tables as appose to the one Person table but how does the booking relationship work?

How would you link Events to perople of all three types?

 

[The_Doc_Man]

Oh, you've just exposed the tip of the iceberg. What do you do if the business owner is also a teach or student?

The answer is that you don't record that fact in the "Person" table. Instead, you have a many-to-one child table that uses the "Person" table prime key as a foreign key and includes a "role" code. Then for students (or teachers) you have a class table. The class table doesn't say who is in the class. It just tells you what the class covers. Your ROLE table has ANOTHER FK to the class table showing that this person is a teacher in class BIO 101 while this other person is a student in BIO 101. (If you have multiple sessions of the same class, you need not only a class table but a SESSION table as an intermediary.)

Now, in theory you could use the same pointer for the code "business owner" to point to the right record in a BUSINESSES table. However, Access won't let you build that relationship correctly. On the other hand, if your role table has another linking field for businesses, you could build that relationship easily enough. It would be a "sparse" link in that some records would not be linked for EITHER of the FK fields. But that is OK, that's why you have inner joins.

 

[Pat Hartman]

The typical structure is to create an entity table. This table assigns the autonumber, holds all the common fields and the entity type code. This is the table that is used in ALL relationships. Then you have a series of class tables that define the fields specific to an entity class. So, for your scenerio you would have three tables. Student, Teacher, and Business owner. Remember NO relationships are made to the class tables except the 1-1 relationship between entity and each class. In your example, the booking would be made with the entity.

If you want to allow a single entity to be part of more than one class, you would remove the entity type code from the entity table and you would determine the class by presence or absence in a particular class table. Of course this could complicate the booking process if that process required knowledge of the entity type.

PS -
1. When naming foreign keys, it is good practice to use the exact same name as the PK to which they relate whenever possible.
2. Guest(s) do not belong in the Event table. There would be a many-side table to hold the attendee data.

 

[cuttsy]

Ha, thats what I love / hate about data modeling. Everytime I think I have a grasp on the basics it just laughs at me

 

[Len Boorman]

Ha, thats what I love / hate about data modeling. Everytime I think I have a grasp on the basics it just laughs at me

Welcome to the club

You never do two applications the same so every time you go through the same head banging interesting fascinating challenging confusing frustrating enjoyeable design process

L