macOS Mac Software Development - Native OS X - SQL

[Outie]

Hello MacRumors!

First of all thanks for the great site and the mac programming section, this is exactly what I was looking for.

Let me start by giving a brief overview of the current project my team and I are currently working on. We are developing a clinical software to be used by private practices. The current details of the project I am not currently at liberty to go into but lets just say its going paperless.

The outline of the project is currently in place as well as the overall design. Currently we are at the point when the idea hits the coding blocks and turns paper into software. The idea we have now is to use Cocoa (Xcode) in combination with MySQL Pro to develop the software.

Do you all have any opinions on the combination of the two. From our experience this should work fine together even with the limitations mysql has as compared to MS SQL Server.

I look forward to your comments.

Chris

 

[Nugget]

I presume you mean "MS SQL Server" and "MC" is just a typo.

I'd strongly urge you to consider using PostgreSQL instead of MySQL. I think you'll find that its licensing is far more agreeable. Moreover, it's going to be much more familiar to any developer who has worked with more traditional SQL servers previously.

MySQL is a weird, unusual beast in the land of SQL servers and has some really mind-boggling oddities which can be difficult to work around if you're accustomed to a more well-rounded SQL environment.

PostgreSQL is particularly compelling if your schema looks like it will be complex and especially if your application profile involves complex SQL and/or involved updating.

 

[Outie]

I presume you mean "MS SQL Server" and "MC" is just a typo.

I'd strongly urge you to consider using PostgreSQL instead of MySQL. I think you'll find that its licensing is far more agreeable. Moreover, it's going to be much more familiar to any developer who has worked with more traditional SQL servers previously.

MySQL is a weird, unusual beast in the land of SQL servers and has some really mind-boggling oddities which can be difficult to work around if you're accustomed to a more well-rounded SQL environment.

PostgreSQL is particularly compelling if your schema looks like it will be complex and especially if your application profile involves complex SQL and/or involved updating.

Nugget,

Thank you for your input! I will research it over the holiday and present it to the team on Monday.

Our project will rely heavily on the SQL server for storage of primarily all of the information and will link to all the images and documents for the patients, so this is a very important part of our software.

Chris

 

[ChrisA]

I presume you mean "MS SQL Server" and "MC" is just a typo.

I'd strongly urge you to consider using PostgreSQL instead of MySQL.

I've used both. MySQL is fasterr of simple applications that do not involve a lot of concurrent access. PostgreSQL is more of an Ocacle clone. Or maybe Oracle was a PostgreSQL clode. (I think Postgres came first) You see Mysql used for simple things like backing of web pages, cookie tracking and others mostly web based stuff. Postgresql is beeter if you are thinging about 50 interreleted tables, four way joins and dozens of cuncurrent users.

One thing you absolutly MUST get right us data integrity. Postgres being "object relational" lets you define data types in the server that you can use in the SQL Query. You can define operators over those types and move much of the logic into the DBMS server. You can get carried away and define types like "blood pressure" that have two floating point components and a defined valid range. In my work I made a tyle call "location" that was a latutude and longitude and defined a distance functon that used great circle method. I could then do a SQL query for "All point within 300 miles of point-B"

For this application I think you are wanting a fully normalized scheema and as much logic checking at the server side as you can think t implement. Speed should be a non issue here as I doubt we are talking about even 1000 transactions per hour, let alone per second. Not only thiat but I can build indexes on functions over user defined types.

Maybe you can guess I'm a big believer in the school of DBMS that wants to place as much of the "business logic" into the server side as possible. I greatly improves the robustness of the system to have the DBMS server enforce a large set of rules.
When you can define data types and functions those rules can be very powerful

Do look at replication of the DBMS or detailed logs. You can't depend on just backing up the files, you'd loose a day's worth of data.

 

[Outie]

ChrisA,

Thanks for your input, I am one to fully agree with you on the DBMS, although I am just starting to look into PostgreSQL I will keep DBMS on top of the list. I proposed this to half my team this morning and from the limited amount of research they did today they are already warming up to the idea.

I can already see the benefit of going PostgreSQL over MySQL. The software will be comprised of a standalone client with a SQL server relationship that will run numerous quieies against a relational database. So already PostgreSQL is looking better and better.

I do have a question for macrumors about getting some extra training on PostgreSQL since none of my database team nor I have had formal experience with it. I am looking at sending 2 or 3 of us to one of there training camps ( PostgreSQL DBA 8 Certification Training Camp ), has anyone attended one and if so how was the experience?

Thanks again guys!

Chris

 

[Outie]

Continuing the post above.. Will postgresql be able to accompdate the following:

- triggers
- SQL quirries
- database size in upwards of 200GB
- multiple concurrnt connections accessing relational db tables
- odbc and hl7 connections (HL7 used for lab interfaces)
- graphical interface to enter information via odbc

In the common client senario the SQL server will maintain all the data that is accesiable to the client (fat or thin) via client software (graphical interface)
that will poll and update tables in the SQL database.

Also please see my previous post in this thread about the PostgreSQL camp.

Thanks

 

[larkost]

PostgreSQL handles triggers in a couple of languages, so you are covered there.

I am going to guess that you meant "SQL queries", and this is definitely there (that the definition of of a SQL server).

And you are probably counting images and files such as that in the 200GB, since it is hard to get there with raw data. I would strongly encourage that you approach this as a 2 part problem: the raw data and the images/files. The raw data (anything that is indexable) goes in the database and the rest goes into a web server (that is about as fast a transfer as you are going to get). When you go to put something new in the system you generate a new key (possibly by a trigger in the database), and then use that as a name (probably in a hierarchy of files with some sort of security on the http layer... maybe certificates) so that you can get to the information fast. caching on the client side will also help.

Multiple connections is part and parcel with a full SQL server. Look into transactions to add an additional layer of safety.

ODBC is there, but HL7 is going to require something else sitting on top of any database in order to handle this. You could look into solutions such as Mirth (http://www.mirthproject.org/), and open source HL7 interpreter.

And on the graphical side, there are lots of programs that can be used as generic front ends for SQL databases, and it all depends on what you need. This is not going to be as integrated as it would with Microsoft SQL Server.

And I will voice one bad thing about postgreSQL: the need to "vacuum" the database from time to time.

 

[Flynnstone]

I have a couple "newbie" type database questions.

Is it a good or bad idea to store pictures (jpegs) inside of a database?
Does this apply if there a LOT of pictures?
How about storing video within a database?

How fast can information be stored in a database? ( I know this is like an "I'm going to the US, do you know of a good restaurant" type question).
Using say a XServe or equivalent.
Anything I can chew on would be appreciated.

 

[pilotError]

Why not leave the images on disk and just store the metadata in the database?

 

[Flynnstone]

Why not leave the images on disk and just store the metadata in the database?

Is this for me?
Yes, I want to know pros and cons.