NHibernate Mapping/Design advice - join tables

Question

Hi,

I have a design issue that I would appreciate some input on.

I would like to make an calendar event system based on the following 4 tables:

tb_events

id, date, summary,

---

tb_group

id, name, parent, lineage,

---

tb_course

id, name,

---

tb_staff

id, name, email,

---

The event class is currently set up as

id
summary
date
calDetails IList<CalDetails>

---

class CalDetail

id
name

---

My dilemma is whether to use one join table - linking the eventID to the ID of a group,course or staff or whether to add group, course staff collections to the event class.

Having a collection for each would be easier to map in hibernate but I'm wondering what kind of performance hit I will get using 3 joins. (The event table may have up to 10-20000 records). Also, as I only need the name property when viewing the event, using the one collection would make it easier to add another kind of 'event detail' to the mix.

If I were to go with the one collection, is it possible to map it in NHibernate. I've looked around for examples but can't find any. Which kind of got me thinking that perhaps this wasn't a good way to go about it.

Anyway, I'd be grateful of any thoughts.

Answer 1

If you do the naive approach of using 3 joins, you may get a huge performance hit. Ayende has some examples of using MultiCriteria to bring performance back in line.

You can also go the subclasses route, if it makes sense.

Answer 2

Be careful not to optimize for performance prematurely. It's fairly unlikely you'll have performance problems, and in the event that they surface they are fairly easy to circumvent. I would start out by going the most frictionless route. You'll be happier that way.