Solved

Please help me with my database modeling

Posted on 2016-10-14
7
32 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 32

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 32

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 108

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 32

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 108

Expert Comment

by:Ray Paseur
ID: 41843693
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now