• 0

Database normalization help [mysql]


Question

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 comment
https://www.neowin.net/forum/topic/1250092-database-normalization-help-mysql/
Share on other sites

4 answers to this question

Recommended Posts

  • 0
  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.

  • Like 1
  • 0

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?

  • 0

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

This topic is now closed to further replies.
  • Posts

    • I got too many apps I use actively. Theres been too often I completely blank on the name of the application and had to manually look through the list for it. Now I'm using Start11 and got my apps sorted in the Start menu by categories so that if I'm looking for Krita i can find it under "Art editors". If MS are going to change it, they should consider making automated categories. Seen some Linux distros do that. But if they do, theres probably gonna be a lot of wrongly categorized apps though, unfortunately.
    • LAV Filters 0.80.0 by Razvan Serea LAVFSplitter is a multi-format media splitter that uses libavformat (the demuxing library from ffmpeg) to demux all sorts of media files. LAV Splitter is a Souce Filter/Splitter required to demux the files into their separate elementary streams. LAV Audio and Video Decoder are powerful decoders with a focus on quality and performance, without any compromises. Supported Formats: MKV/WebM, AVI, MP4/MOV, MPEG-TS/PS (including basic EVO support), FLV, OGG, and many more that are supported by ffmpeg! LAV Filters are based on ffmpeg and libbluray and is aimed to offer a all-around solution to perfect playback of file-based Media as well as Blu-rays. LAV Filters 0.80.0 changelog: LAV Splitter NEW: Introduced the IURLSourceFilterLAV interface to allow opening URLs with custom user agent and referrer NEW: Added support for WebP images Changed: Increased the length of the advanced subtitle selection field, so its no longer cut off after 255 characters Changed: Improved buffering behavior on badly interleaved video files Fixed: Audio streams with an unknown/unsupported codec are no longer selected for playback, as long as others are present Fixed: Improved accuracy of reported FPS from AviSynth scripts LAV Video NEW: D3D11 support for HEVC 4:2:2 and 4:4:4 hardware decoding NEW: Dolby Vision extension metadata is exported for renderers to use Changed: Added additional media types to support more video streams Changed: Updated dav1d for significant AV1 decoding improvements Fixed: Improved handling of H.264 4:4:4 files encoded by certain versions of x264 Fixed: VP9 DXVA2/D3D11 decoding could result in artifacts on some clips Fixed: Decoding ProRes reports more accurate color details LAV Audio Changed: Added support for additional ADPCM audio codecs Download: LAV Filters 0.80.0 | 15.5 MB (Open Source) View: LAV Filters Website | Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • Haven't really used it much for years, not specific to Windows 11.. it's there but I mostly ignore it. I do install StartAllBack though bust mostly for the other bits that it brings. I prefer the "alt-space" type launchers (KRunner in Plasma, Flow in Windows or the like), a lot less clutter and more smarts like bookmark/history searches and other useful plugins.
    • Calibre 8.5 by Razvan Serea  Calibre is an open source e-book library management application that enables you to manage your e-book collection, convert e-books between different formats, synchronize with popular e-book reader devices, and read your e-books with the included viewer. It acts as an e-library and also allows for format conversion, news feeds to e-book conversion, as well as e-book reader sync features and an integrated e-book viewer. Calibre's features include: library management; format conversion (all major ebook formats); syncing to e-book reader devices; fetching news from the Web and converting it into ebook form; viewing many different e-book formats, giving you access to your book collection over the internet using just a browser. Calibre 8.5 changelog: New features The scrollbars used in calibre in light mode are now the same style as the ones in dark mode, this improves the contrast making the scrollbar more accessible Kobo driver: add an option to change the how the Kobo displays series numbers using a template. Manage data files dialog: Add a button to cancel remaining books when managing multiple books Kobo driver: add support for new Tolino firmware Bug fixes Prevent Windows 11 from starting a conhost.exe process for every calibre worker process E-book viewer: Improve highlight grouping with recurring chapter names When sending emails to amazon and pocketbook use random English text instead of UUIDs for subject/body. Improved news sources NYTimes WSJ Financial Times Eenadu Fokus.se Business standard Go comics NZ Herald TLS Magazine Download: Calibre 8.5 | Portable | ~200.0 MB (Open Source) Download: Calibre for MacOS | 316.0 MB Download: Calibre for Linux View: Calibre Home Page | Calibre Screenshot Get alerted to all of our Software updates on Twitter at @NeowinSoftware
  • Recent Achievements

    • First Post
      emptyother earned a badge
      First Post
    • Week One Done
      Crunchy6 earned a badge
      Week One Done
    • One Month Later
      KynanSEIT earned a badge
      One Month Later
    • One Month Later
      gowtham07 earned a badge
      One Month Later
    • Collaborator
      lethalman went up a rank
      Collaborator
  • Popular Contributors

    1. 1
      +primortal
      674
    2. 2
      ATLien_0
      277
    3. 3
      Michael Scrip
      220
    4. 4
      +FloatingFatMan
      168
    5. 5
      Steven P.
      162
  • Tell a friend

    Love Neowin? Tell a friend!