0

I want to model the following scenario in the database:

I have a Package, comprising of independent Activities and Hotel:

Package P:

  • Activities A1, A2, A3
  • Hotel H

Activities and Hotel are independent entities which have their own price defined at the time of definition of entity.

When an entity is added to the package, it's price can be changed (only specific to the package). Hence, every package would have a unique price for the activities/hotels.

E.g. (Defining activities and hotel):

A1 - 10$
A2 - 20$

H1 - 100$

(Adding activities and hotel to package):

Package p;
p.addActivity("A1", 15);
p.addActivity("A2", 25);
p.addHotel("H1", 50);

yields => 

p
 - A1 - 15
 - A2 - 25
 - H1 - 50

Database Side:

Definition:

  • Activity:

    Activity-id, StartDate, EndDate, Price

  • Hotel:

    Hotel-id, StartDate, EndDate, Price

After adding to package

  • Package table:

Package-id, Hotel-id, Activity-id

  • Package-Activity price table:

    Package-id, Activity-id, Price //package-id and activity-id serve as unique key

  • Package-Hotel price table:

    Package-id, Hotel-id, Price //package-id and hotel-id serve as unique key

I need some feedback on the design that I've come up with. Have I over complicated this ? Is there a simpler/better way to do this ? Also, as I was writing this, I figured a package can have many Hotels and Activities, so will need to factor that in here. Have I fragmented this too much since, for each price lookup of an entity I will be performing a join ?

EDIT

Found a relevant link on performance with Joins: When and why are database joins expensive?

Community
  • 1
  • 1
brainydexter
  • 19,826
  • 28
  • 77
  • 115

1 Answers1

2

You actually do need five tables here.

You have three entities: activities, hotels, and packages (assuming each package has some data which is not hotel- or activity-related). You have two relationships, both many-to-many, namely package-hotel and package-activity. Each relationship row is annotated with a particular price.

Five tables is the way to go. No, having to do a join is not a problem: it's better than having non-normal data. You can denormalize for performance later if necessary.

Borealid
  • 95,191
  • 9
  • 106
  • 122
  • I'm a little confused. So, we have 1 table for all activities. Package can contain multiple activities, so I need one-to-many relationship there. I'd assume Package-activities table is the way to go for that. I want to keep a separate table for pricing, so I'd have a Package-activities-price for that as I listed in my question. Correct ? – brainydexter Jan 24 '12 at 09:12
  • 1
    @brainydexter: No. First off, a package can contain multiple activities, but a given activity can be in multiple packages. That's a many-to-many relationship. Now, when you have a many-many join table, you don't have to limit yourself to just two fields, so the price can be in that table along with `package_id` and `activity_id`. Why would you keep a separate table for just the prices? It would have to *also* have the IDs in order for you to know to what the price refers. – Borealid Jan 24 '12 at 14:16