Jump to content



Photo

Mogodb Vs Mysql Decision?

mogodb mysql nosql security data integrity

  • Please log in to reply
8 replies to this topic

#1 Stuart

Stuart

    Advanced Member

  • Members
  • PipPipPip
  • 142 posts
  • LocationBirmingham, UK

Posted 15 December 2011 - 12:51 PM

In a current project I have a large database that basically holds people and their personal information e.g. each contact can have multiple phone numbers, email addresses, residential addresses etc... As a result data security and integrity are very important.

I started of sketching out the database schema and it seems fairly straight-forward. I'd create a people table which contains the individual people and then a series of other tables to hold their personal information each in a one-to-many relationship. E.g. Each person can have multiple phone numbers.

All seemed fine until I started thinking through the use cases.

If someone updates their mobile number and saves their details I would essentially have to delete all their numbers and then add them all again because you can't easily tell if details were deleted, updated or inserted to the contact. I think any effort to package the existing ID with each number would simply be messy. So first, is there anything wrong with deleting and re-inserting all one-to-many relationships? It will lead to big gaps in the primary key which I guess can be fixed by calling something like repair etc... For reference this database will have several million users.

This seemed messy so I considered storing the one-to-many relationships as serialised objects within a single table because then I wouldn't have to worry about maintaining FK relationships and having large gaps in the primary keys. This led me to looking at NoSQL solutions as this seems to be basically what they do. I read the mogoDB article from Larry's newsletter and thought it might be worth considering.

Any thoughts?

PS. In general I only have to update or fetch the objects I don't have to search based on a phone number etc...
  • 0

#2 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,866 posts
  • LocationState College, PA (USA)

Posted 16 December 2011 - 8:07 PM

If someone updates their mobile number and saves their details I would essentially have to delete all their numbers and then add them all again because you can't easily tell if details were deleted, updated or inserted to the contact. I think any effort to package the existing ID with each number would simply be messy. So first, is there anything wrong with deleting and re-inserting all one-to-many relationships? It will lead to big gaps in the primary key which I guess can be fixed by calling something like repair etc... For reference this database will have several million users.


Well, first of all, you don't have to do deletes and inserts. You can do REPLACE, which has the same net effect. Or you could just build in the logic to only run an UPDATE query when the user changes something. You absolutely don't want to do anything about the "gaps" in the primary key sequence.

This seemed messy so I considered storing the one-to-many relationships as serialised objects within a single table because then I wouldn't have to worry about maintaining FK relationships and having large gaps in the primary keys. This led me to looking at NoSQL solutions as this seems to be basically what they do. I read the mogoDB article from Larry's newsletter and thought it might be worth considering.

Any thoughts?


I'm not entirely convinced of the merits of non-relational databases, at least not for the general user. But this would seem like a good candidate for MongoDB.
  • 1

#3 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,061 posts
  • LocationOslo, Norway

Posted 19 December 2011 - 1:37 AM

This is not really that hard. You just have to make sure all phone numbers also have an unique identifier alongside a foreign key to the user table.

Users (id, firstname, lastname, day_of_birth, etc....)
phones (phone_id, user_id*, number)
Email (email_id, user_id*, email)
so on...

To find all emails, numbers, etc, just do a query with "SELECT * FROM table WHERE user_id = $user_id".

To delete/modify a number, use the etc_id like "DELETE from table WHERE some_id = $some_id LIMIT 1/UPDATE table set phone = $variable WHERE some_id = $id". The phone/email/etc id, you add to the URL of a delete/update button link to take you to update/delete.php?id=$variable.

This will make sure data integrity is strong. Use auto_increment on phone_id, email_id and resident_id, and tie them to a user. Make sure phone numbers, emails and residents are unique. They should be anyway. A would recommend a Nation table and city table too if you need to make sure residents have high integrity.
  • 2

#4 Stuart

Stuart

    Advanced Member

  • Members
  • PipPipPip
  • 142 posts
  • LocationBirmingham, UK

Posted 19 December 2011 - 11:10 AM

Thanks for your replies guys. Indeed Antonio the apps will be storing the primary key in their local storage and passing this up during any updates - deletes handled simply by their lack of presence in the uploaded data. As tempting as a NoSQL solution is in terms of the coding elegance of simply storing the objects I don't feel I know enough about NoSQL solutions in terms of security to ensure the protection of millions of people's data at this time. Some of the security features are either not present in NoSQL solutions or would require rolling my own versions. So for now at least I'm sticking with a the more mature MySQL database (time permitting I might build both to see which produces the best product in terms of security, scalability and elegance. Thanks again for your input.
  • 0

#5 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,866 posts
  • LocationState College, PA (USA)

Posted 19 December 2011 - 1:09 PM

There's a strong argument to doing what you know best, so I think you're making the right decision.

By the way, I'm thinking of possibly doing a chapter on MongoDB when I do the 3rd edition of my PHP 5 Advanced book next year.
  • 2

#6 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,061 posts
  • LocationOslo, Norway

Posted 22 December 2011 - 12:18 PM

Looking forward to that, Larry.
  • 0

#7 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,061 posts
  • LocationOslo, Norway

Posted 30 September 2012 - 7:55 AM

Picking up this. Did you include anything on MongoDB? I'm strongly considering using NoSQL for a new project.
  • 0

#8 Larry

Larry

    Administrator/Writer

  • Administrators
  • 3,866 posts
  • LocationState College, PA (USA)

Posted 1 October 2012 - 6:10 AM

I actually did not include anything on NoSQL in the book. Just didn't have space after all the expanded OOP stuff.

Working on getting you an ebook now...
  • 0

#9 Antonio Conte

Antonio Conte

    Advanced Member

  • Members
  • PipPipPip
  • 1,061 posts
  • LocationOslo, Norway

Posted 3 October 2012 - 6:31 PM

No problem, and thank you very much. :)
  • 0





Also tagged with one or more of these keywords: mogodb, mysql, nosql, security, data integrity