Multiple databases and complex queries
The program I am looking to create will use a PHP front end to create, read, update, and delete from multiple databases. The context is a medical practice management program that is web based.
Not only is the ability to create these databases important (e.g. a client information DB including info like name, address, phone number, etc) but also I would like to retain the ability to search using any and all parameters when trying to look up a client. It's also worth mentioning that there will be multiple user created databases such as a client database, a patient database, inventory and charge code database, among others.
My questions to you all are:
Is SQL the right choice for this application?
If so, what would be the most efficient and best performing way to structure such a database?
Any other thoughts?
SQL is just a (fairly) standardized language of querying databases that support it. The choice is which DBMS to use: MySQL, PostgreSQL, MS SQL, etc., and there is no single correct answer.
There is normally no real need to create multiple databases, though sometimes you may need to integrate with existing systems that have their own databases. But if you are building this more or less from the ground up, then stick with one database, and use a combination of separate table of different types of things to track, along with columns within those tables that let you differentiate which data belongs to which user/client/customer/whatever.
Last edited by NogDog; 06-25-2013 at 10:34 PM.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
How to Ask Questions the Smart Way
(not affiliated with this site, but well worth reading)
So to summarize, create one database but multiple tables within for different types of data, e.g. one table for client info and another for inventory line items.
The one basic form I created allows a user to enter a client's first and last name, address, city, state, zip code, and I believe phone number. This is my code (the pertinent part):
This writes successfully to the SQL table, but I wonder if A) this is the proper way to set it up and B) how one would go about searching the database and displaying that output to the user?
// Get values from form
$lastname = $_POST['lastname'];
$firstname = $_POST['firstname'];
$address1 = $_POST['address1'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zipcode = $_POST['zipcode'];
// Insert data into mysql
$sql="INSERT INTO $tbl_name(lastname, firstname, address1, address2, city, state, zipcode)VALUES('$lastname', '$firstname', '$address1', '$address2', '$city', '$state', '$zipcode')";
Thanks much, new to SQL and PHP so bear with me.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)