Please help me with my database modeling

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?
burnedfacelessAsked:
Who is Participating?
 
Ray PaseurConnect With a Mentor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
burnedfacelessAuthor Commented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ste5anSenior DeveloperCommented:
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
 
ste5anConnect With a Mentor Senior DeveloperCommented:
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
 
burnedfacelessAuthor Commented:
Thanks Ray you came though with this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.