• 0

Database normalization help [mysql]


Go to solution Solved by Asmodai,

Question

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.

post-1865-0-62349100-1426442254.png

Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0
+Asmodai

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
Link to post
Share on other sites
  • 0
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?

Link to post
Share on other sites
  • 0
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

Link to post
Share on other sites
  • 0
Angel Blue01

Thanks!

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By News Staff
      Get this Interactive Learn to Code Bundle at 73% off for just $29.99
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can save 73% off the Interactive Learn to Code Bundle. Start writing codes and programs from scratch with 9 interactive courses on SQL, JavaScript, PHP, jQuery, BootStrap, and more.



      This deal consists of the following courses:

      An Interactive SQL Tutorial for Beginners: Introduction to SQL
      Getting Started with SQL Doesn't Have to Be Hard An Interactive JavaScript Course for Beginners
      Learn JavaScript Interactively with a One-of-a-Kind JS Online Course Interactive jQuery Tutorial: Learn jQuery Step-by-Step
      Take an Interactive jQuery Training Course for Beginners to Quickly Master jQuery Events, Effects & More Learn PHP Online: PHP Basics Explained in an Interactive & Fun Manner
      An Interactive Tutorial for Beginners Who Want to Master PHP Basics in a Non-Boring Way The Complete Solidity Smart Contract Guide
      Master Solidity Programming Through Hands-On Experience Master Python Fundamentals the Fun Way: An Interactive Python Tutorial
      A Python Training Course for Absolute Beginners Who Wants to Master the Language Without Getting Bored A Responsive Web Design Course: Find Out How to Create a Responsive Website
      Make a Website Mobile-Friendly with a Step by Step, Responsive Web Design Tutorial Bootstrap Tutorial: Learn to Create Dynamic Websites in a Pinch
      Find Out How to Use Bootstrap the Fun Way by Using an Interactive Course An Interactive Java Tutorial: Learn by Practice!
      Learn Java From Scratch by Combining Theory with Hands-On Coding Experience Good to know
      Length of time users can access this course: lifetime Certification of completion included Redemption deadline: redeem your code within 30 days of purchase For a full description, specs, and instructor info, click here.

      Here's the deal:
      This Interactive Learn to Code Bundle normally costs* $112, but you can pick it up for just $29.99 for a limited time - that represents a saving of $82.01 (73%) off.

      >> Get this deal, or learn more about it <<
      See all discounted Online Courses. This is a time-limited offer.


      Get $1 credit for every $25 spent · Give $10, Get $10 · 10% off for first-time buyers.

      Not for you?
      If this offer doesn't interest you, why not check out the following offers:

      The Win Your Dream 2020 Tesla Model 3 Giveaway
      The Nintendo Gaming Bundle Giveaway 20% off Ivacy VPN subscription with coupon code IVACY20 NordVPN subscription at up to 68% off for a 2 year plan Private Internet Access VPN subscription at up to 71% off Unlocator VPN or SmartDNS unblock Geoblock with 7-day free trial Disable Sponsored posts · Other recent deals · Preferred partner software

      Disclosure: This is a StackCommerce deal or giveaway in partnership with Neowin; an account at StackCommerce is required to participate in any deals or giveaways. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through our branded deals site, and it all goes toward the running costs.

      running costs.

    • By News Staff
      Save 95% off this Google Cloud Certifications Practice Tests + Courses Bundle
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store where you can save 95% off this Google Cloud Certifications Practice Tests + Courses Bundle. Hone your expertise on the Google Cloud platform with 43 hours of video content and 1,000+ practice questions for in-demand GCP certifications.



      This bundle consists of the following courses:

      Google Cloud Certified Professional Cloud Architect
      Demonstrate Your Knowledge of Designing, Developing, & Managing Secure, Robust, and Dynamic Solutions on GCP Google Cloud Certified Professional Cloud Security Engineer
      Validate Your Skills to Design & Implement a Secure Infrastructure on Google Cloud Platform Google Cloud Certified Professional Data Engineer
      Make Data-Driven Decisions by Collecting, Visualizing, & Transforming Data Google Cloud SQL Deep Dive
      Implement & Manage Network Architectures on Google Cloud Platform Google Cloud Certified Associate Cloud Engineer
      Demonstrate Your Knowledge of Deploying Applications, Monitoring Operations, & Managing Enterprise Solutions on GCP Google Cloud Certified Professional Cloud Network Engineer
      Implement & Manage Network Architectures on Google Cloud Platform Google Cloud Certified Professional Cloud Developer
      Design, Build, Test, Deploy, Integrate & Manage Scalable Applications on GCP Good to know
      Certificate of completion included Length of time users can access after purchase: lifetime Redemption deadline: redeem your code within 30 days of purchase For a full description, specs, and instructor info please click here.

      Here's the deal:
      This Google Cloud Certifications Practice Tests + Courses Bundle normally costs* $639 but it can be yours for just $29.99 for a limited time, that's a saving of $609.01 (95%) off the price.

      >> Get this deal, or learn more about it here <<
      See all Online Courses on offer. This is a time limited deal.
      Get $1 credit for every $25 spent · Give $10, Get $10 · 10% off for first-time buyers.

      Not for you?
      If this offer doesn't interest you, why not check out the following offers:

      The Win Your Dream 2020 Tesla Model 3 Giveaway
      The Nintendo Gaming Bundle Giveaway 20% off Ivacy VPN subscription with coupon code IVACY20 NordVPN subscription at up to 68% off for a 2 year plan Private Internet Access VPN subscription at up to 71% off Unlocator VPN or SmartDNS unblock Geoblock with 7-day free trial Disable Sponsored posts · Other recent deals · Preferred partner software

      Disclosure: This is a StackCommerce deal or giveaway in partnership with Neowin; an account at StackCommerce is required to participate in any deals or giveaways. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through our branded deals site, and it all goes toward the running costs.

    • By News Staff
      Free eBook: Your First Week With Node.js
      by Steven Parker

      Claim your complimentary copy for free, before the offer expires.



      What's it about?


      A beginner's introduction to Node and its related technologies.

      Used by some of the biggest companies today, Node is blazingly fast. Taking JavaScript, a ubiquitous language among developers, we'll get you up and running with guides on when to use Node, NPM, Security, as well as connecting with MySQL, MongoDB and more.

      How to get it
      Please ensure you read the terms and conditions to claim this offer. Complete and verifiable information is required in order to receive this free offer. If you have previously made use of these free offers, you will not need to re-register. While supplies last!

      >> Your First Week With Node.js - free eBook <<
      Offered by SitePoint, view their other free resources. Limited time offer.

      Not for you?
      That's OK, there are other free eBooks on offer you can check out here, but be aware that these are all time-limited offers. If you are uncomfortable sharing your details with a third-party sponsor, we understand. Or via our preferred partner:

      How can I disable these posts? Click here.

      Disclosure: A valid email address is required to fulfill your request. Complete and verifiable information is required in order to receive this offer. By submitting a request, your information is subject to TradePub.com's Privacy Policy.

    • By News Staff
      Save 73% off the Interactive Learn to Code Bundle - now just $29.99
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can save 73% off the Interactive Learn to Code Bundle. Start writing codes and programs from scratch with 9 interactive courses on SQL, JavaScript, PHP, jQuery, BootStrap, and more.



      This deal consists of the following courses:

      An Interactive SQL Tutorial for Beginners: Introduction to SQL
      Getting Started with SQL Doesn't Have to Be Hard An Interactive JavaScript Course for Beginners
      Learn JavaScript Interactively with a One-of-a-Kind JS Online Course Interactive jQuery Tutorial: Learn jQuery Step-by-Step
      Take an Interactive jQuery Training Course for Beginners to Quickly Master jQuery Events, Effects & More Learn PHP Online: PHP Basics Explained in an Interactive & Fun Manner
      An Interactive Tutorial for Beginners Who Want to Master PHP Basics in a Non-Boring Way The Complete Solidity Smart Contract Guide
      Master Solidity Programming Through Hands-On Experience Master Python Fundamentals the Fun Way: An Interactive Python Tutorial
      A Python Training Course for Absolute Beginners Who Wants to Master the Language Without Getting Bored A Responsive Web Design Course: Find Out How to Create a Responsive Website
      Make a Website Mobile-Friendly with a Step by Step, Responsive Web Design Tutorial Bootstrap Tutorial: Learn to Create Dynamic Websites in a Pinch
      Find Out How to Use Bootstrap the Fun Way by Using an Interactive Course An Interactive Java Tutorial: Learn by Practice!
      Learn Java From Scratch by Combining Theory with Hands-On Coding Experience Good to know
      Length of time users can access this course: lifetime Certification of completion included Redemption deadline: redeem your code within 30 days of purchase For a full description, specs, and instructor info, click here.

      Here's the deal:
      This Interactive Learn to Code Bundle normally costs* $112, but you can pick it up for just $29.99 for a limited time - that represents a saving of $82.01 (73%) off.

      >> Get this deal, or learn more about it <<
      See all discounted Online Courses. This is a time-limited offer.
      Get $1 credit for every $25 spent · Give $10, Get $10 · 10% off for first-time buyers.

      Not for you?
      If this offer doesn't interest you, why not check out the following offers:

      The Nintendo Gaming Bundle Giveaway 20% off Ivacy VPN subscription with coupon code IVACY20 NordVPN subscription at up to 68% off for a 2 year plan Private Internet Access VPN subscription at up to 71% off Unlocator VPN or SmartDNS unblock Geoblock with 7-day free trial Disable Sponsored posts · Other recent deals · Preferred partner software

      Disclosure: This is a StackCommerce deal or giveaway in partnership with Neowin; an account at StackCommerce is required to participate in any deals or giveaways. For a full description of StackCommerce's privacy guidelines, go here. Neowin benefits from shared revenue of each sale made through our branded deals site, and it all goes toward the running costs.

    • By Hamza Jawad
      Azure Data Studio's August release brings Notebook improvements and more
      by Hamza Jawad



      Last month, Microsoft announced new Azure tools and infrastructure improvements at its Inspire 2020 event. A few days later, the tech giant unveiled NFS 3.0 support for its Azure Blob storage service. Today, the latest version of Azure Data Studio has been released, with the introduction of Notebook improvements, a new Jupyter Books picker feature, and more.

      The changelog for this month reads as follows:

      For starters, two new capabilities have been introduced in the cell toolbar, including arrow key icons to change the ordering of cells, and a separate option to convert cells from text cells to code blocks and vice versa. The aforementioned are features that have already been available for a while in standard Jupyter environments, such as Google's Colab, so it is not much of a surprise that they are being brought to Notebooks in Data Studio as well.

      Moving on, a new capability to find and load remote Jupyter Notebooks with ease has been added. The notebook viewlet now has a "Add Remote Jupyter Book" option, which shows a dialogue box when accessed. This allows users to directly choose from among pre-defined locations such as GitHub to quickly load the relevant repositories.

      And finally, the search experience in Notebooks has also been enhanced, with a more dynamic way to locate specific content. All Notebooks listed in the viewlet can be sifted through with a single search, with search results marking out all relevant Notebooks, as showcased in the image above.

      The full list of bug fixes made in the August release can be viewed here. The latest version of Azure Data Studio can be downloaded here, while exact release notes have been made available here.