• 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 97% off this Complete C Suite Programming Bundle
      by Steven Parker

      Today's highlighted deal comes via our Online Courses section of the Neowin Deals store, where you can save 97% off this Complete C Suite Programming Bundle. Master this foundational, general-purpose programming language with 80 hours of specially curated instructions on C++, C#, SQL, and more.



      This deal consists of the following courses:

      Complete Modern C++
      The Most Comprehensive C++ Tutorial with C++11/14/17 Features Master C# And SQL by Building Applications
      Become a Better Software Developer by Learning How to Build Applications Using C# & SQL Learn C# by Building Applications
      Understand the Concepts of C#6 & C#7 and Use Them to Build Real World .NET Console Apps The Complete Introduction to C++ Programming
      Learn All the Basics of C++ by Practice & Create Various Basic C++ Applications Structural Design Patterns in Modern C++
      Learn Structural Design Patterns in Depth & Their Implementation in Modern C++ Creational Design Patterns in Modern C++
      Learn Creational Design Patterns in Depth & Their Implementation in Modern C++ Object Oriented Analysis, Design & Programming with UML
      Learn Object-Oriented Analysis, Design & Programming Using UML in C++, Java and C# Detecting Memory Leaks in C/C++ Applications
      Apply Techniques to Detect & Isolate Memory-Related Errors in Visual Studio C/C++ Pointers & Applications
      Learn the Application of Pointers Such As Usage in Dynamic Arrays, Linked Lists, & More Beginner Object Oriented Programming In C# and .NET Core
      Learn Object-Oriented Programming Fundamentals & Climb Up the Developer Hierarchy Good to know
      Length of time users can access this course: lifetime Certificate of completion included Updates 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 Complete C Suite Programming Bundle normally costs* $1,990, but you can pick it up for just $49.99 for a limited time - that represents a saving of $1,940.01 (97%) 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.



      Enter giveaways: Polycade Home Arcade | $5K in cash | $10K in Crypto Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2 year subscription at up to 68% off Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Neowin Store for our preferred partners. 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 Hamza Jawad
      SQL Server Big Data Clusters CU10 brings important new capabilities
      by Hamza Jawad



      With the release of SQL Server 2019, Microsoft brought Big Data Clusters (BDC) for customers as a way to utilize both structured and unstructured data. Running exclusively on Linux containers that are hosted on Kubernetes, these clusters can be deployed both on-premise and on the cloud. Last year, the tech giant released the cumulative update 5 (CU5) for SQL Server 2019, focusing on expanding capabilities offered through BDC.

      Today, Microsoft has unveiled the SQL Server Big Data Clusters CU10 release. With this update, a few new important capabilities have been introduced. These have been highlighted as follows:

      Notably, when it comes to base image upgrading, the documentation for the CU10 release states that due to the stricter security requirements of Ubuntu 20.04, users may face issues if they attempt to use BDC to connect to SQL Server instances before SQL Server 2017. If such a case arises, they should refer to the following solution.

      When upgrading existing BDC, the new CU10 images will be utilized once the usual in-place upgrade to the CU has been performed. Additionally, High Availability capabilities for Hadoop's key management service (KMS) components further improves the Encryption at Rest feature set. After the upgrade, all namenode pods will have a KMS, not only offering increased high availability, but also providing a performance boost for encryption operations on encryption zones.

    • By News Staff
      Get this Backend Developer Bootcamp Bundle for only $29.99
      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 96% off this Backend Developer Bootcamp Bundle. This 31-hour course bundle takes you behind the scenes of web development: C#, SQL, .NET Core, and more.



      This bundle includes the following courses:

      Learn C# By Building Applications
      Understand the Concepts of C#6 & C#7 and Use Them to Build Real World .NET Console Apps Master C# and SQL by Building Applications
      Learn How to Build Real-World Applications Using C# & SQL Beginner Object Oriented Programming in C# and .NET Core
      Learn Object-Oriented Programming Fundamentals & Climb Up the Developer Hierarchy Learn ASP.NET Core and SQL by Writing ToDo API
      Write an API with Step-by-Step Instructions Using ASP.NET Core, SQL Server, & T-SQL Microsoft SQL Server & T-SQL Course for Beginners
      Learn about the Benefits of SQL & How SQL Server Stores Data Good to know
      Length of time users can access this course: lifetime Certificate 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 Backend Developer Bootcamp Bundle normally costs $995, but you can pick it up for just $29.99 for a limited time - that represents a saving of $965.01 (96%) 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 free eBooks on offer you can check out here.



      Home Gym Giveaway | Bitcoin (BTC) Investment Giveaway Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2 year subscription at up to 68% off Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Neowin Store for our preferred partners. 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: 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 98% off this All-in-One Coding Skills 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 the All-in-One Coding Skills Bundle. Boost your IT skill set and master coding with 25 hours on Scratch, Kodu, Python, SQL, Flutter, C++, and ITIL.



      This bundle consists of the following courses:

      Scratch 3.0 Programming Masterclass: Learn by Creating 12 Funny Games
      Build Games in Scratch 3.0 Including Snake, Pac-Man, Racing Game, & Elsa from Frozen Learn Coding by Creating 3D Games with Kodu Game Lab
      Be an Expert Coder in the Exciting, Funny Kodu Game Lab Python MTA 98-381 Complete Preparation Course
      Your Complete Python Resource to Succeed in the Python Certification MTA 98-381 Exams! MTA 98-361: Software Development Fundamentals Preparation Course
      Everything You Need to Pass the Microsoft MTA 98-361 Exam with Step-by-Step Examples SQL: Mastering MySQL & MariaDB with Examples for Beginners
      Understand SQL Using the MySQL Database, How to Install MySQL Service & Design Databases Master Flutter & Dart with a Complete Showcase Mobile App
      Build iOS & Android Apps with a Showcase Example Based on Dart and Flutter Framework C++: Master C++ with Step-by-Step Examples for Beginners
      Start from the Basics & Go All the Way to Creating Your Own Applications ITIL 4 Certification Exam: A Complete Preparation Masterclass to Master ITIL
      A 3-Step Process to Master ITIL & Pass the Exam Good to know
      Length of time users can access this course: lifetime This plan is only available to new users 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 All-in-One Coding Skills Bundle normally costs $1,592, but you can pick it up for just $29.99 for a limited time - that represents a saving of $1,562.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 free eBooks on offer you can check out here.



      Home Gym Giveaway | Bitcoin (BTC) Investment Giveaway Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2 year subscription at up to 68% off Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Neowin Store for our preferred partners. 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: 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
      Pay What You Want for this Java Master Class 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 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.



      How does it work?
      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

      Here's the deal:
      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. Beat the Leader's price and get entered into the epic giveaway. 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 free eBooks on offer you can check out here.



      Home Gym Giveaway | Bitcoin (BTC) Investment Giveaway Ivacy VPN - 5 year subscription for just $1 per month NordVPN - 2 year subscription at up to 68% off Private Internet Access VPN - subscriptions at up to 71% off Unlocator VPN or SmartDNS - unblock Geoblock with 7-day free trial Neowin Store for our preferred partners. 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: 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.