Solved

Please help me with my database modeling

Posted on 2016-10-14
7
47 Views
Last Modified: 2016-10-14
Screen-Shot-2016-10-14-at-3.51.13-AM.png
Here is my database schema or whatever it is called. The admin page just logs the user in (if I need to add an index to that let me know). The contact and billing tables hold email recipients that forms send to, so an administrator user can alter that.

In the near future we will be texting alerts in the case of a hurricane, and we will be texting people to let them know if there water is off. Here's the problem - I think I normalized those three tables correctly (phone number, subdivision, and system).

However we don't want one person to have the same number in one subdivision. They might own multiple properties in different subdivisions so I was thinking about keeping phone unique but something is telling me this might cause problems.

Also if we text a customer a mass warning message they will get multiple if they are signed up for multiple subdivisions.

What solutions are available for this? Can I configure MySQL to do this (i.e. LIMIT clause). Or should this be better coded with PHP?
0
Comment
Question by:burnedfaceless
  • 3
  • 2
  • 2
7 Comments
 
LVL 33

Expert Comment

by:ste5an
ID: 41843223
Without knowing your requirements, concrete help is not possible.

But some hints, derived from your description so far:

* Don't store passwords. NEVER. Store a salted hash instead, with a separate salt per user.
* Write down your requirements (and user stories) in a easy to understandable form. This means especially: Don't use complex phrasing, don't use loanwords. When using  technical terms, check that their usage is harmonized in your user groups. Don't use abbreviations.
* "Admin", "user" and sometimes "customer" are persons.
* Contact and billing in your case form a super-sub class relation.
* Who is the person assigned to these contact addresses?
* A subdivision must have a parent.
* Your entity and especially attribute names are not following good naming conventions. There is even an ISO norm for that: ISO/EC 11179
0
 

Author Comment

by:burnedfaceless
ID: 41843403
My requirements are for an employee to send out a mass text message. This will be done by subdivision (see table subdivision). I also want to include the ability to text every customer in case we have a hurricane like the one we just had. I also plan to allow employees to text by water system, in case there is something unique to their system.

The problems are what if a customer has more than one phone number in the database for different subdivisions (different properties)? A Unique Index would prevent this from being entered and without a unique index they would get two messages (or more) depending on how many subdivisions were registered. This is my primary concern.

Disregard the contact and billing forms. Our website has two forms and I coded something where they can add recipients so they don't have to call me to do it. That way they can manage who gets form emails.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41843436
Gathering the requirements is a top-down process.

My requirements are for an employee to send out a mass text message.
Taking this requirement means that you need to define employee and to define mass text message.

So define all your open keywords first.  

The problems are what if a customer has more than one phone number in the database for different subdivisions (different properties)?
It's not a problem right now. Cause I don't know what customer is, why has he phone numbers?

[..] subdivisions were registered. This is my primary concern.
It makes semantically absolute no sense to talk about subdivisions without having divisions.

Cause a database model is always a semantical model.

These things may sound like nit-picking, but they are not. The way to a correct entity relationship model and thus to a relational database schema leads over clarifying these points first.

Or in other words: Building a house on sand is possible. But without groundwork?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 250 total points
ID: 41843519
Sidebar note: here is how we handle passwords now-a-days.
https://www.experts-exchange.com/articles/28768/Password-Hashing-in-PHP.html

Why not just have one big table for your consolidated list of all of the contacts?  In that table you would have the email, phone and subdivision.  Your query would SELECT the rows you need to notify.  Easy to write, easy to test!  If you have less than a few million contacts, normalization might not really be needed.  For more on this subject, make a Google search for the exact phrase "Should I Normalize my Database" and read the very interesting arguments on both sides of the question.
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 41843536
For sure. What other side?

http://databases.about.com/od/specificproducts/a/Should-I-Normalize-My-Database.htm

1) Wrong. When you need a more performance for a web site, use caching.
2) Wrong. When you're banging your head on the desk over a complex model, then read and understand the requirements and the documentation first. Cause it's not possible to create an entity relationship model or even derive the requirements from a database schema.
3) Maybe. Why do I need a relational database for a prototype?

Heretics and infidels everywhere.. ;)
0
 

Author Closing Comment

by:burnedfaceless
ID: 41843620
Thanks Ray you came though with this.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 41843693
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question