• Sign in to Neowin Faster!

    Create an account on Neowin to contribute and support the site.

  • 0
Sign in to follow this  

Database normalization help [mysql]

Question

Angel Blue01    8

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.

post-1865-0-62349100-1426442254.png

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0
+Asmodai    637

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.

  • Like 1

Share this post


Link to post
Share on other sites
  • 0
briley    5

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?

Share this post


Link to post
Share on other sites
  • 0
lj300    8

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

Share this post


Link to post
Share on other sites
  • 0
Angel Blue01    8

Thanks!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.