0

I have the following tables: 1) Event 2) Agenda 3) Registrant_Type 4) Fee

Relationship: Event to Agenda: one-to-many (one event consists of zero, one or many agenda; one agenda belongs to only one event)

Event to Registrant_Type: one-to-many (one event consists of zero, one or many registrant types; one registrant type belongs to only one event)

Event to Fee: one-to-one (One event consists of only one fee (registration fee))

Agenda to Fee: one-to-one (One agenda consists of only one fee (agenda fee))

Registrant_Type to Fee: one-to-one (One registrant type consists of only one fee (registration fee))

How do I create tables based on these relationship?

Roy.hpr
  • 59
  • 3
  • 10
  • Do you really need a table of fees? Isn't it just a number you could put in a column in each of the tables that have fees? What other attributes does a fee have that suggests it should be in a table? – Barmar Sep 12 '14 at 02:30
  • @BarmarInside Fee table, I have Pricing_Schedule attributes (early bird price, late comer price etc). Besides, I also have a Discount_Code table relates to Fee table. The relationship between Fee and Discount_Code is: one-to-many (one Fee consists of many discount codes wherease one discount code only belongs to one fee). – Roy.hpr Sep 12 '14 at 02:38
  • @Barmar Inside Fee table, I have Pricing_Schedule attributes (early bird price, late comer price etc). Besides, I also have a Discount_Code table relates to Fee table. The relationship between Fee and Discount_Code is: one-to-many (one Fee consists of many discount codes wherease one discount code only belongs to one fee). – Roy.hpr Sep 12 '14 at 02:39
  • You can either put both endpoints of 1:1 in the same table or use separate tables, the "standard" solution being the single table. More info [here](http://stackoverflow.com/questions/12318870/when-i-should-use-one-to-one-relationship/12321323#12321323). – Branko Dimitrijevic Sep 12 '14 at 12:35

1 Answers1

0

Put a foreign key for event in agenda. Put a foreign key for event in registrant_type.

For the one-to-one relationships, you have two options that would work:

  1. Put foreign keys for fee in event, agenda, and registrant_type.

This will work, but won't enforce the rule that the relationship has to be 1:1 instead of 1:many. If you want to enforce that rule, you'll need to do it in your application or using a trigger.

  1. Use the primary key in event, agenda and registrant_type as your foreign keys for fee.

This enforces a 1:1 relationship because the PK's can't have repeat values. Your fee PK could auto-increment, but then you would have to set the PK's in the other tables manually to correspond to the matching record in fee. This wouldn't work well if you can't add your fee to the database first (for example, if you create events first and then later decided what the fee will be).

John Meinken
  • 439
  • 4
  • 10