#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539

    Using PHP to enforce database rules


    I've recently decided to make some database schema changes and use a superset/subset pattern for a couple of tables (Questions about designing a couple tables)

    Say I have the following three tables:

    Code:
    people
    -id
    -name
    -email
    -phone
    -username
    -etc
    
    users
    -id (FK to people.id)
    -someOtherStuff
    
    contacts
    -id (FK to people.id)
    -someOtherStuff
    Now, throw in a couple of business rules:
    1. email cannot be null for users, but it could be null for contacts.
    2. username must be unique for users, but need not be unique for contacts.


    Now, my above structure cannot enforce these rules. My options are:
    1. Remove email and username from people, and put them in both users and contacts. In reality, there are more than these two that will have to be moved, and a big advanage of superset/subset is to make similar properties common.
    2. Don't worry about having the DB enforce these rules, and just use the application.


    I would appreciate opinions of others on the best course to take.

    Thank you
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,940
    Rep Power
    9647
    I'd go for #1 myself. Even though the fields may be named the same, they behave differently for the two types.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,122
    Rep Power
    539
    I think I will go somewhere in between.

    If it will cause real problems if not met, I will do as you recommend.

    If a business rule states that a field must be provided, but it will not cause any real hardship if not, I will leave it to the application to validate that the value is provided.

    Comments on this post

    • shophiarajan47 agrees
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2014
    Posts
    28
    Rep Power
    0
    As far as I understand the same content if is placed in two tables and are not correlated than there is not need to make them a common attributes for example say email in user and in contact both are different as per you rule, so they must be not moved as a common property.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    381
    Originally Posted by shophiarajan47
    As far as I understand the same content if is placed in two tables and are not correlated than there is not need to make them a common attributes for example say email in user and in contact both are different as per you rule, so they must be not moved as a common property.
    why do you have to come in, repeat what others have said previously making it look like you are actually answering?

    Sorry but if someone else has said the same answer, you don't need to repeat it..

    Sorry if I sound rude, but please try not to bump posts that have already been answered by simply re-iterating a previous reply

IMN logo majestic logo threadwatch logo seochat tools logo