?
Solved

Please help me with my database modeling

Posted on 2016-10-14
7
Medium Priority
?
75 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 35

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 35

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
Video: Liquid Web Managed WordPress Comparisons

If you run run a WordPress, you understand the potential headaches you may face when updating your plugins and themes. Do you choose to update on the fly and risk taking down your site; or do you set up a staging, keep it in sync with your live site and use that to test updates?

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 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 35

Assisted Solution

by:ste5an
ste5an earned 1000 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 111

Expert Comment

by:Ray Paseur
ID: 41843693
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

762 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