Solved

Please help me with my database modeling

Posted on 2016-10-14
7
55 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 110

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 110

Expert Comment

by:Ray Paseur
ID: 41843693
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

726 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