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

Rails
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

2

On 24 October 2012 17:08, Kevin McCaughey [email protected] wrote:

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

On Wed, Oct 24, 2012 at 12:08 PM, Kevin McCaughey [email protected]
wrote:

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.

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

–
Dave A., the T. Rex of Codosaurus LLC,
secret-cleared freelance software developer
taking contracts in or near NoVa or remote.
See information at http://www.Codosaur.us/.

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

On Wed, Oct 24, 2012 at 1:00 PM, Kevin McCaughey [email protected]
wrote:

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

–
Dave A., the T. Rex of Codosaurus LLC,
secret-cleared freelance software developer
taking contracts in or near NoVa or remote.
See information at http://www.Codosaur.us/.

7

On Wed, Oct 24, 2012 at 12:39 PM, Dave A.
[email protected] wrote:

I am looking at all this from the staff point of view - I want staff
spelled that way, and books the Smith room), and simplify the Bookings
stuff, I’d probably:

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.

9

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 H.'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.

10

Ooops forgot to attach image.

11

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.

12

On Wed, Oct 24, 2012 at 2:56 PM, Kevin McCaughey [email protected]
wrote:

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

–
Dave A., the T. Rex of Codosaurus LLC,
secret-cleared freelance software developer
taking contracts in or near NoVa or remote.
See information at http://www.Codosaur.us/.

13

On 24 October 2012 21:46, Kevin McCaughey [email protected] wrote:

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.

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

*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:

15

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.