2

What database design would you suggest for the following example?

Example: Imagine a bus with some people inside. Every person has some attributes like name, etc. Also there are driver- and passenger-specific attributes. Every few hours the roles change - meaning, that the driver becomes a normal passenger and one of the passengers becomes the driver. What would be an elegant way to model this? The project I'm working on is written in Ruby but I guess it doesn't really matter.

I thought about either using AASM (a state machine) or to work with a "Current-Role"-table which includes the user_id and the current role. But this seems kind of wrong to me. Thanks in advance for your suggestions!

Tulains Córdova
  • 39,449
Peter Piper
  • 141

3 Answers3

2

Conceptual ER Model

enter image description here

  • PERSON has person's attributes
  • DRIVER has attributes that apply only to drivers. No need to delete this info when if the person changes role from passenger to driver. The attributes stay there since the person can become a driver again.
  • ROLE, well... passenger, driver, etc.
  • PERSON_ROLE, says what person is in what bus and with what role. Besides being just a join table, it can hold attributes like SEAT_NUMBER (the driver would have a special seat number like, say, 0.
  • BUS, bus attributes
Tulains Córdova
  • 39,449
  • If Driver is a Role, why you modeled Driver also as a component? What's the relationship between Driver - Person? Driver or passenger is just a temporary status of a Person. – Laiv Dec 11 '16 at 14:16
  • 1
    @Laiv This is a conceptual database model, not a class diagram. It doesn't have "components" but entities that would eventually decome database tables. Table DRIVER holds columns for attributes that only apply to drivers. The 1:1 rel between PERSON and DRIVER is because drivers are also persons, and PERSON has columns for things like name, gender, etc. Remember OP wants a database and that drivers have columns that don't apply to all persons. Also the role of the person (however temporary) is held in the table PERSON_ROLE. – Tulains Córdova Dec 11 '16 at 15:45
  • I think the question lack on details. Looks like OP is asking for db schema but AASM has nothing to do with db schema. Neither has it the tags object-oriented, design-patterns nor architectural-patterns... – Laiv Dec 11 '16 at 15:53
  • @Laiv I also noticed that but the question seems to boil down to: database, model, attributes... so I guess he is asking for a database design (which he calls "architechture").. – Tulains Córdova Dec 11 '16 at 15:57
  • Well, that seems more reasonable to me. – Laiv Dec 11 '16 at 15:59
  • sorry for the misunderstanding - I'll change "architecture" to "database design". – Peter Piper Dec 11 '16 at 17:48
  • @TulainsCórdova - thanks a lot for your detailed answer! – Peter Piper Dec 11 '16 at 17:48
1

It appears you need to model people on the bus that are all passengers. Some passengers may be drivers (a sub-type of passenger) and require additional attributes. It is possible that some attributes exist only passengers that are not drivers. That would be problematic if the same person keeps switching type (which you describe as role). I would research modelling sub-types.

Which driver is driving the bus is an attribute of the bus. In this case it would be limited to the passenger on the bus, preferably a driver. Normally I would model this as a relationship table joining bus and passenger with a start-time attribute, and either a duration or end-time. There are normally constraints on overlapping intervals.

BillThor
  • 6,270
0

Focus on the more complex piece first. I think it is tracking what is going on during a trip. Instead of only saving the current status, I would have a table that is more of a log. It should tell you,

  • On every trip, where is everyone seated

  • what time do they change seats

  • what is the role of that seat (Driver or Passenger).

    If a person is in the driver's seat during a given period, there is additional information about that person's driving attributes.

For buses with individually identifiable seats, you could track that as well.

enter image description here

Linking the Bus to the Trip, but then, you only get one bus per trip record.

Community
  • 1
JeffO
  • 36,816