My first database model - would you look at it pls?

rubyonrails-talk
1

Hi,

I am new to rails and am making a meeting manager that manages meetings for people at different locations. So it should be able to keep track of meetings of people at different places and also know what a place is being used for.

Here is a link to the model (I have also attached as a file): http://www.kevinmccaughey.org/relationships/relationships.html

And here is an explanation for the diagram:

NOTES:

Person: Just a person in the organisation.

Meeting: A collection of attendees meeting at place and particular time.

Place: A physical building somewhere, with rooms (or a single room) that can be used as a venue for a meeting.

Attendee: A person attending a unique meeting. A person could be an attendee at multiple meetings during the week.

Booking: A time and place for a meeting. There can be more than one meeting within the same booking, or the booking can be in a different room. This is because there may be lots of places within a building to meet, and lots of meetings going on at the same time.

Check: Please ignore this for the time being. I am attaching it to attendees for future use in a queue system (I do not want to poll the attendees for events, I will create checks).

Many thanks for any comments you have on this. It is my first time creating the model and I have already removed many<->many by design in case someone is wondering why there are no many<->many links.

Kevin

Attachments: http://www.ruby-forum.com/attachment/7826/Base_Classes.jpg

2

Hi,

I am new to rails and am making a meeting manager that manages meetings for people at different locations. So it should be able to keep track of meetings of people at different places and also know what a place is being used for.

Here is a link to the model (I have also attached as a file): http://www.kevinmccaughey.org/relationships/relationships.html

And here is an explanation for the diagram:

NOTES:

Person: Just a person in the organisation.

If the person is going to log on to the system then it would be more usual to call him/her a user. Then it will better fit with one of the authentication gems.

Meeting: A collection of attendees meeting at place and particular time.

Place: A physical building somewhere, with rooms (or a single room) that can be used as a venue for a meeting.

Attendee: A person attending a unique meeting. A person could be an attendee at multiple meetings during the week.

I would call this attendance, not attendee as this record is not the actual attendee but is a link to the person who is the attendee. Then you can say meeting has_many attendances and has_many people through attendances.

Booking: A time and place for a meeting. There can be more than one meeting within the same booking, or the booking can be in a different room. This is because there may be lots of places within a building to meet, and lots of meetings going on at the same time.

Not quite sure I understand this. A booking contains fields with room and time, yet it has_many meetings. This only makes sense if there are several meetings in the same room at the same time. Then you also have a time in the meeting, which can presumably be different to the time in the booking. Clarification required I think.

Check: Please ignore this for the time being. I am attaching it to attendees for future use in a queue system (I do not want to poll the attendees for events, I will create checks).

Don't worry about building in stuff for future possible requirements. It is usually a waste of time as the requirements will change or you will think of a better way of doing it. Just design what you need at the moment, make sure you have good coverage with automated tests (that you write as or before you write the code) and then re-factor as necessary as requirements evolve.

Not at all a bad start for beginner however. :slight_smile:

Colin

3

Overall, this looks pretty good to me!

What you've done for many<->many looks like pretty much the standard Rails way. I assume you're using "has_many meetings, through: attendees" on the people, and "has_many people, through: attendees" on the meetings, right?

The only thing I see that I might do differently on *that* relationship, is that "attendee" sounds to me like it's a person. It is, from the meetings' point of view, but not from the people's. So, I'd probably call it something like "attendances". Not a big deal, and for all I know your app may be such that "attendees" really does fit better after all.

As for the rest of it, I don't quite grok the Booking versus Meeting dichotomy. I would guess that you want Bookings to manage the transactions between the entity holding the Meeting, and the Place that owns the room. In that case I'd be tempted to move the room and time to the Meeting, since those are essential components of what the name Meeting brings to my mind. You could still have a Booking model, in case someone wants to arrange multiple Meetings in one financial transaction (giving you somewhere to record contract details, confirmations, payments, etc.), or drop that and just have Meetings connect directly to their Place.

There are other refinements that could be made, depending on the info you need to store. Do you need to record any info about each room? Can a Meeting occupy multiple rooms? Each of these will complicate the overall model a little bit.

Anyway, you're off to a great start! I wouldn't have guessed that it's your first. Maybe you've designed some databases for other purposes, just not for Rails before?

-Dave

4

"I assume you're using "has_many meetings, through: attendees" on the people, and "has_many people, through: attendees" on the meetings, right?"

I was wondering if I could do that - so thanks, I will :slight_smile:

And as Colin and Dave both pointed out, "attendance" does fit better than "attendee".

As for the Bookings table, the reasoning behind this is in case there are multiple meetings going on in the same building, at the same time - there needs to be a way to differentiate them. If I simply use a room number, then we still have the issue of a many to many for Meeting<->Place. So, I thought that rather than just do a straight join table, I could logically separate the meetings into "bookings" (if that makes sense).

I am looking at all this from the staff point of view - I want staff (Person) to be able to fill in a time-table for their weeks and someone else be able to view meetings happening (i.e. a manager has a look at what his staff are doing for the week, can look at any one member of staff at a given point of time to see where they should be, are they safe etc).

As for keeping track of the rooms, I am not too worried about that.

Thanks very much Colin and Dave for your comments - feel free to add more :slight_smile:

5

It's my first Rails database, and my first ever relational database for anything :slight_smile: I have been reading up on it a lot :wink:

6

As for the Bookings table, the reasoning behind this is in case there are multiple meetings going on in the same building, at the same time - there needs to be a way to differentiate them. If I simply use a room number, then we still have the issue of a many to many for Meeting<->Place. So, I thought that rather than just do a straight join table, I could logically separate the meetings into "bookings" (if that makes sense).

I am looking at all this from the staff point of view - I want staff (Person) to be able to fill in a time-table for their weeks and someone else be able to view meetings happening (i.e. a manager has a look at what his staff are doing for the week, can look at any one member of staff at a given point of time to see where they should be, are they safe etc).

As for keeping track of the rooms, I am not too worried about that.

Hmmmm. I'm thinking that in order to minimize conflicts due to typos (someone books the Smythe room, but the next clerk doesn't know it's spelled that way, and books the Smith room), and simplify the Bookings stuff, I'd probably:

- Move the time to the Meeting, with starts_at and ends_at times.

- Make a Room model, with at least the name, even if you don't need to track the floor, size, capacity, etc. At the very least, it could be useful to populate dropdowns or do autocompletion. This would belong_to Place, which I'd probably rename to Building since that seems to be the level of granularity.

- Slightly repurpose Booking to be a join table between Meeting and Room, so that a Meeting could be held in several Rooms (as often happens at hotels for conventions that need various sized rooms), and a Room could of course be used for many Meetings.

(One could argue that you don't need an explicitly named join table for this, and can just use a "has_and_belongs_to_many" (HABTM) relationship. However, I've heard many people say that it's a royal pain to retrofit that to a "has many :through" (HMT) relationship when you find you need to actually put data on the relationship, so I tend to start with HABTM in the first place.)

So, in order to find what Meetings all a Place's Rooms will be used for, today, you just say:

  some_place.rooms.meetings.where("DATE(meetings.starts_at) = ?", Date.today)

Nice and simple....

It's my first Rails database, and my first ever relational database for anything :slight_smile: I have been reading up on it a lot :wink:

You have learned well, young padawan.... :wink:

-Dave

7

I wonder if something like that couldn't be address with PostgreSQL and hstore where you store a null hstore hash that has common names NAME2=>NULL,NAME2=>NULL, since searching that is easy.

8

Thanks Dave - I have put the new diagram up at: http://www.kevinmccaughey.org/relationships/relationships.html

Is that what you meant? I am musing over it at the moment.

Attachments: http://www.ruby-forum.com/attachment/7828/Base_Classes.jpg

9

Thanks Dave - I have put the new diagram up at: http://www.kevinmccaughey.org/relationships/relationships.html

That doesn't show me something new. Maybe there's something caching it too much. However:

Attachments: http://www.ruby-forum.com/attachment/7828/Base_Classes.jpg

That shows me a new diagram. It's almost what I meant, but you've got Bookings having many Rooms, whereas I was thinking they would *belong to* Rooms. (And you've got Meetings having *one* Booking, whereas I was thinking they'd have many.) Bookings would be essentially nothing but a join table between Meetings and Rooms, just like Attendances is between Meetings and People. Just like a Person can have many Meetings and vice-versa, through many Attendances, each of which belongs to one of the other model, a Meeting can have many Rooms and vice-versa, through many Bookings, each of which belongs to one of the other model.

In diagrammatic terms, diamondize the Booking end of the link from Meeting, and reverse the one from Room. Like this (words chopped to make the whole thing fit on one line, and adopting the User suggestion):

User ()-<> Attend <>-() Mtg ()-<> Bookg <>-() Rm <>-() Bldg

Or, to de-UMLify it:

User 1-* Attend *-1 Mtg 1-* Bookg *-1 Rm *-1 Bldg

BTW, one of the things that I find confuses new Rails people about the difference between "has" and "belongs_to", is which model has to store the ID of the other model (as a foreign key). They way I usually remember it is that if something belongs to me, it might have my name written on it, but I'm certainly not going to have the "names" of everything that belongs to me, written on me. So in Rails, if model Dog has_many legs, the legs table has a dog_id column. (Or if you've got dog legs and cat legs and so on all mixed up in one table, you can tell Rails the column name is owner_id or something like that.)

-Dave

10

Thanks again Dave - I have attached the updated jpeg (I use cloudflare for my site so I think that is caching it :wink:

I am only at Chapter 7 of the Rails Tutorial (Michael Hartl's great site), but I think chapter 8 should teach me how to do the actual code for it! I been sort of making my own app as I go along you see.

My last big project was in C and Assembler, but we didn't have any relational stuff to do as it was all writing BIOS/Firmware.

11

Ooops forgot to attach image.

Attachments: http://www.ruby-forum.com/attachment/7829/Base_Classes.jpg

12

What I am wondering though is: if I have 1000 users with about 15 meetings each a week, will it be fast enough? I am running on just 1 linked at present using PostgreSQL.

13

In terms of database size that is trivial. If all 1000 users tried to log on at once however then you could be in trouble.

Colin

14

Thanks a lot guys! I really appreciate the help. I don't have 1000 users yet as I am out of work, but this system is something I was inspired by a work problem, and I hoping it gives me enough experience to maybe get a junior programming job once I am finished. I was a programmer some years ago but recently my career has been elsewhere.

I would love to get back into programming again and, despite some frustrations, I am loving Rails and databases :slight_smile:

Thanks again for your help.

15

*Please excuse my bad typing in the previous post - just getting my first coffee of the morning before starting learning again and my brain is still asleep :wink: