Database normalization help [mysql]

[Angel Blue01]

My organization is hosting a charity event, and they've asked me to develop a Web-based registration system, using MySQL and PHP. I need some help getting the database structure correct, based on the registration forms they've created on paper. I've made similiar systems for them in the past, but these were never more than one database table in complexity. I took a course in database design in college but I hcan't had do deal with this many tables or db normalization in a long time.

 

Participants (registrants) are either individuals or families, with some fields in common to both types, such as contact info. Each family has multiple members with unique information per person, such as name and t-shirt size. Individual registrants have these fields as well.

Each registrant is supposed to raise money via donors. Donors can sponsor many registrants for any amount each.

 

I'm working in MySQL Workbench for the first time and made an EER diagram of my tables. What I'm particularly confused about is whether I have the individuals correctly set in their own table in addition to their entry in the registrants table.

 

Thanks.

[+Asmodai MVC]

  On 18/03/2015 at 14:34, briley said:

Have you considered combining the familymembers and individuals table into a single table? It might be easier when creating your app to keep all the "humans" associated with a registration in one place (i.e. to find out how many t-shirts to make).

 

Also, what is the purpose of the donors-donations table?

 

I'm not sure I fully grasp the scenario but on quick glance I would say it makes more sense to combine donors, individuals, and family members into a single "people" or similar table.  That table should also have fname and lname which should not be in the registrants table. (which might be better to call registrations as it is not people).  Likewise I'd have a single addresses table and remove the duplicate fields from donors and registrants.

 

So the donors table might have personid, addressid, and donorid.

registrants might have regid, personid, familyid, and addressid (where each record contained either a personid OR a familyid, this effectively replaces the type enum as well)

familymembers might have familyid and personid (you might also want a families table that has the familyid as a key in between these)

table people might have personid, fname, lname, age, email, phone(cell), and tshirtsize.

table addresses might have addressid, address, city, state, phone(home), and zip.

 

The purpose of the donors-donations table seems to be to allow a many to many relationship between donors and donations.  i.e. a single donor can have many donations and a single donation can come from many donors.

[briley]

Have you considered combining the familymembers and individuals table into a single table? It might be easier when creating your app to keep all the "humans" associated with a registration in one place (i.e. to find out how many t-shirts to make).

 

Also, what is the purpose of the donors-donations table?

[lj300]

for example if registrant is a family, what would you put on the fname of the registrants table?

 

i would get rid of individuals; create a table called members and manage al members on one table, being individual or family

then a registrant with only one record asociated in members is then an individual

 

i would remove fname and lname from registrants and move them to members

[Angel Blue01]

Thanks!