• 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
This topic is now closed to further replies.
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By News Staff
      Save 98% off this 2021 Premium Learn to Code Certification Bundle
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where for only a limited time, you can save 98% off this Premium Learn to Code 2021 Certification Bundle. Amp up your programming skill set with 270+ hours of content on the leading coding languages — taught by top-rated instructors Ft. Rob Percival, Nick Walter, and more!.



      This deal consists of the following courses:

      JavaScript Beginner Bootcamp
      Understand the JavaScript Language & Learn to Create and Launch Interactive Webpages SwiftUI Apps for All Apple Platforms
      Learn the True Magic of SwiftUI & Make Apps and Widgets for iPhone, Mac, iPad, Apple Watch, and AppleTV The Complete C# Programming Course
      Master C# & .NET Framework with Code Challenges, Exercises, & Real-Life Examples The Complete Web Developer Course 2.0
      Learn By Doing: Build 25 Websites & Mobile Apps Alongside Top-Rated Online Coding Instructor, Rob Percival! SwiftUI: The Complete Developer Course
      Learn Everything You Need to Know About the SwiftUI Framework & Leverage All of Its Great New Technologies 2020 Complete Ruby on Rails 6 Bootcamp
      Start From the Basics of Ruby on Rails & Go All the Way to Creating Your Own Applications Learn Python for Data Analysis & Visualization
      Gear Yourself with Necessary Skills for Expert-Proven Data Science Techniques Raspberry Pi Essentials & Extras
      Broaden Your Raspberry Pi's Horizons with This 1-Hour Crash Course PHP & MySQL: Certification Course for Beginners
      Learn to Build Database-Driven Web Applications Using PHP & MySQL Bootstrap & jQuery: Certification Course for Beginners
      Create Fully Animated, Interactive, Mobile Responsive Web Pages Using Bootstrap & jQuery Library HTML, CSS, & JavaScript: Certification Course for Beginners
      A Comprehensive Guide for Beginners Interested in Learning HTML, CSS, & JavaScript to Build Interactive Web Pages Java Foundations
      Master the General Purpose & Object-Oriented Language of Java to Enhance Your Skills for Mobile Development Making APIs with Django & Python
      Use the REST Framework to Quickly Make APIs for Your Django Projects Making Websites with Python & Django 3: The Ultimate Beginner's Guide
      Boot Up the Django 3 Web Framework Written In Python & Develop Websites Fast JavaScript Master Class
      A Comprehensive Course Just About Every Fact on JavaScript You Could Ever Hope to Know Python for Everyone
      Learn the Basic Syntax of How Python Language Works How to Get a Job as a Web Developer
      Tips, Tricks & Industry Secrets to Help You Land That Web Development Job The Python Mega Course: Build 10 Real World Applications
      A Complete Practical Python Course for Beginners & Intermediates! Master Python 3 by Building Actual Python Apps ROS2 For Beginners
      Master the Key ROS Concepts to Create Powerful & Scalable Robot Applications The Complete Web Developer Course: Build 20 Websites
      Learn the Fundamentals of HTML, CSS & JavaScript by Building 20 Real-World Websites Machine Learning for Absolute Beginners
      Learn to Build Machine Learning Models Using Spreadsheet Data Java Interview Questions: Data Structures & Algorithms
      The Best Java Interview Guide with the Most Common Beginner Questions Asked by FANG Companies

      The Ultimate Amazon Honeycode Guide: Build 7 Apps
      Build Apps to Automate Data Entry: Make Inventory Management Apps & More Easy Business Projects

      Learn Flutter & Dart: Complete App Development Projects
      Build Apps in Flutter, Google's Cross-Platform Framework & Code with Its Dart Programming Language

      Complete Data Science Training with Python for Data Analysis
      Learn Statistics, Visualization, Machine Learning & More

      C++ Programming for Beginners
      Master C++, An In-Demand, High-End Language Used for Programs, Games, Development Libraries, & More

      The Complete Front-End Web Development Course
      Take a Hands-on Approach to Learning Real Programming Skills

      Good to know
      Certification of completion included Length of time users can access this course: lifetime 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 Premium Learn to Code 2021 Certification Bundle normally costs* $4,056, but you can pick it up for just $59.99 for a limited time - that represents a saving of $3,996.01 (98%) 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 Ivacy VPN - 5 year subscription for just $0.99 per month NordVPN - 2 year subscription at up to 68% off +3 months for free! Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Subscribe to Neowin - for $14 a year, or $28 a year for Ad-Free experience Disable Sponsored posts · Neowin Deals · Free eBooks · Neowin Store

      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 MyNick
      I have three tables:
       
      Product(carrots, potatoes)
      id name
       
      Element(calcium, sodium)
      id name content100(content in 100 grams of product)
      0 calcium 20 milligrams
       
      product_element
      id product_id element_id
       
      I cannot understand to which table the data on "Content in 100 grams of product" belongs
      The product does not apply to the item either.
       
      Any help please.
    • By News Staff
      Pay What You Want for the Java Master Class Bundle at Neowin Deals
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where for only a limited time you can Pay What You Want for this Java Master Class Bundle. Kickstart your coding career with 50+ hours of training in the world's most popular programming language.



      What's the deal?
      With the Pay What You Want bundles, you can get something incredible for as little as you want to pay. And if you beat the average price, you’ll receive the fully upgraded bundle! Included in this Pay What You Want deal, are the following courses:

      Pay What You Want (as little as $1) for the unlocked item:

      Java Game Development: Create a Flappy Bird Clone
      Use the World's Most Popular Programming Language to Create Your First Game

      Or beat the average price to also get the following items:

      Oracle Java SE 8 Certification Course
      Get Your Java Skills Online in This 14-Hour Primer

      Java Web Services and XML: ORACLE Exam Preparation
      Enhance Your Java Toolkit with an Introduction to XML

      JavaFX: Building Client Applications
      Walk Through Building Your First JavaFX App in 3 Hours

      Mastering Java Swing & GUI Applications
      Learn How to Write GUI Applications in Java

      Java Web Technologies Course
      Get Java-Savvy & Prep for an Oracle Certification Exam

      Professional Web Scraping with Java
      Collect Mounds of Valuable Data by Web Scraping

      Java Hibernate Object/Relational Mapping (ORM) Course
      Come to Grips with Java's Hibernate Tool

      Java Spring MVC Quickstart Course
      Create Web Apps Using the Java Spring Framework

      Learn Java From Scratch
      Go From Beginner to Expert in the Web's Most Popular Language - Over 9 Hours of Training

      What's the benefit?
      The bundle represents an overall retail value of $960 But you can Pay What You Want for the unlocked course (as little as $1) Beat the average price and you'll take home the entire bundle. Qualify for the giveaway!
      Beat the Leader's price and get entered into the epic giveaway - plus get featured on the Leaderboard!

      >> Pay What You Want for this Java Master Class Bundle <<
      See other Pay What You Want deals 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?
      That's OK, there are other deals 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. Check out the Neowin Store for our preferred partners.



      The Win Your Dream 2020 Tesla Model 3 Giveaway Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2 year subscription at up to 68% off +3 months for free! Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Subscribe to Neowin - for $14 a year, or $28 a year for Ad-Free experience Disable Sponsored posts · Neowin Deals · Free eBooks · Neowin Store

      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
      Save 98% off the 2021 Premium Learn to Code Certification Bundle
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can save 98% off this Premium Learn to Code 2021 Certification Bundle. Amp up your programming skill set with 270+ hours of content on the leading coding languages — taught by top-rated instructors Ft. Rob Percival, Nick Walter, and more!.



      This deal consists of the following courses:

      JavaScript Beginner Bootcamp
      Understand the JavaScript Language & Learn to Create and Launch Interactive Webpages SwiftUI Apps for All Apple Platforms
      Learn the True Magic of SwiftUI & Make Apps and Widgets for iPhone, Mac, iPad, Apple Watch, and AppleTV The Complete C# Programming Course
      Master C# & .NET Framework with Code Challenges, Exercises, & Real-Life Examples The Complete Web Developer Course 2.0
      Learn By Doing: Build 25 Websites & Mobile Apps Alongside Top-Rated Online Coding Instructor, Rob Percival! SwiftUI: The Complete Developer Course
      Learn Everything You Need to Know About the SwiftUI Framework & Leverage All of Its Great New Technologies 2020 Complete Ruby on Rails 6 Bootcamp
      Start From the Basics of Ruby on Rails & Go All the Way to Creating Your Own Applications Learn Python for Data Analysis & Visualization
      Gear Yourself with Necessary Skills for Expert-Proven Data Science Techniques Raspberry Pi Essentials & Extras
      Broaden Your Raspberry Pi's Horizons with This 1-Hour Crash Course PHP & MySQL: Certification Course for Beginners
      Learn to Build Database-Driven Web Applications Using PHP & MySQL Bootstrap & jQuery: Certification Course for Beginners
      Create Fully Animated, Interactive, Mobile Responsive Web Pages Using Bootstrap & jQuery Library HTML, CSS, & JavaScript: Certification Course for Beginners
      A Comprehensive Guide for Beginners Interested in Learning HTML, CSS, & JavaScript to Build Interactive Web Pages Java Foundations
      Master the General Purpose & Object-Oriented Language of Java to Enhance Your Skills for Mobile Development Making APIs with Django & Python
      Use the REST Framework to Quickly Make APIs for Your Django Projects Making Websites with Python & Django 3: The Ultimate Beginner's Guide
      Boot Up the Django 3 Web Framework Written In Python & Develop Websites Fast JavaScript Master Class
      A Comprehensive Course Just About Every Fact on JavaScript You Could Ever Hope to Know Python for Everyone
      Learn the Basic Syntax of How Python Language Works How to Get a Job as a Web Developer
      Tips, Tricks & Industry Secrets to Help You Land That Web Development Job The Python Mega Course: Build 10 Real World Applications
      A Complete Practical Python Course for Beginners & Intermediates! Master Python 3 by Building Actual Python Apps ROS2 For Beginners
      Master the Key ROS Concepts to Create Powerful & Scalable Robot Applications The Complete Web Developer Course: Build 20 Websites
      Learn the Fundamentals of HTML, CSS & JavaScript by Building 20 Real-World Websites Machine Learning for Absolute Beginners
      Learn to Build Machine Learning Models Using Spreadsheet Data Java Interview Questions: Data Structures & Algorithms
      The Best Java Interview Guide with the Most Common Beginner Questions Asked by FANG Companies

      The Ultimate Amazon Honeycode Guide: Build 7 Apps
      Build Apps to Automate Data Entry: Make Inventory Management Apps & More Easy Business Projects

      Learn Flutter & Dart: Complete App Development Projects
      Build Apps in Flutter, Google's Cross-Platform Framework & Code with Its Dart Programming Language

      Complete Data Science Training with Python for Data Analysis
      Learn Statistics, Visualization, Machine Learning & More

      C++ Programming for Beginners
      Master C++, An In-Demand, High-End Language Used for Programs, Games, Development Libraries, & More

      The Complete Front-End Web Development Course
      Take a Hands-on Approach to Learning Real Programming Skills

      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 Premium Learn to Code 2021 Certification Bundle normally costs* $4,056, but you can pick it up for just $59.99 for a limited time - that represents a saving of $3,996.01 (98%) 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?
      That's OK, there are other deals 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. Check out the Neowin Store for our preferred partners.



      The Win Your Dream 2020 Tesla Model 3 Giveaway Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2-year plan 68% off +3 months for free (total of 27 months) Private Internet Access VPN - 3 year plan at 86% off ($2.19/month) Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Subscribe to Neowin - for $14 a year, or $28 a year for Ad-Free experience Disable Sponsored posts · Neowin Deals · Free eBooks · Neowin Store

      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 Scylla
      Back in 2016, a couple of friends and I have founded Turn-Based Tactics, a Steam group dedicated to our fav genre and run by gamers for gamers. It sort of snowballed as we now have over six thousand members. Every Saturday we post an update wrapping up the news of the week: new releases and expansions, announcements, betas, demos, crowdfunding campaigns, etc.
       
      Over the time this precious data was compiled into a database. It currently includes over a thousand (!) games sorted by release date, subgenre, dev, country, price, reviews, metascore… Let me know if you spot any game missing or if you have any other feedback. Note that we had to limit ourselves to Steam as including other platforms would represent too much of a scope.