Using MySQL or XML flat file for a database

[foozball3000]

We're developing a small program that has to be populated from a shared database on their network. The best solutions in this case is either a MySQL datafile/database or an XML Flat File. As the data won't be more than 50,000 records, both solutions are plausable.
The key hook on this is that security has to be very tight (Hence why we're not using MS Access). What's the Pro's and Cons between XML and MySQL?

 

[Necuno]

stay away from the [-]voodoo[/-] flat files.

/vote MySQL

 

[Gladiator]

or just use the free version of sql server 2005 / 2008, express edition, limited to one cpu, and a 2gb database.

but u get the power of linq/elinq

dont quote me on the dbase size limit, but theres also a 4gb ram limit

 

[Gnome]

There are people still using "flat" files? My word, I thought those days ended around 10 years back.

Any DBMS would be better than a flat-file, would be faster also and it's easier, no win scenario if you ask me, add into that the MySQL or even better PostgreSQL is free and there's no competition.

 

[Necuno]

There are people still using "flat" files? My word, I thought those days ended around 10 years back.

Any DBMS would be better than a flat-file, would be faster also and it's easier, no win scenario if you ask me, add into that the MySQL or even better PostgreSQL is free and there's no competition.

heh, we are rewriting some of those dark ages systems which are still in use to .net 3.5 + sql

tons of fun.

 

[RSkeens]

I would definitely not use a flatfile here.

 

[FarligOpptreden]

...and what if the internet host doesn't offer any database support, or the cost of hosting a database is exorbitant? In any of those cases, imho, an XML-based solution could work quite nicely.

 

[Necuno]

...and what if the internet host doesn't offer any database support, or the cost of hosting a database is exorbitant? In any of those cases, imho, an XML-based solution could work quite nicely.

• Curious, how are you going to implement security, by file rights ?
  
• What if there needs to be a change to the structure of the data it is going to be allot more work to rework the xml than an actual database. poor bugger who have to do this when the time comes.
  
• how does a transaction work with xml?, are you going to remove the bits that successed from the bits the failed ?
  
• access speed ?, going to be slower i guess using a xml.
  
• what happens when two or more people write to the same file ?
  
• table, record locking ?
  
• if you would have to display 3000 items with 50 a page; xml- hello memory, dabase- request next set ?
  
• data relations, integrity, indexes, restraints ?

Isn't mySQL free ?, we are definitly past the day and age where pc's costs a arm and a leg.

 

[dequadin]

UPFRONT DISCLAIMER © 2009 FarligOpptreden THIS POST CONTAINS "LINQ"

I also vote for mySQL.

As far as security goes in the XML solution, trusting/trying to get Windows file sharing to do that for you is asking for trouble.

An XML solution could work, LINQ to XML again here LINQ will give full CRUD on the XML flat file...

But if you look at the spec, mainly:

• from a shared database on their network.
• security has to be very tight.

mySQL gives both of these features by "default" and since it's not going "online" hostying isn't a problem. XML will require extra work to get these two done!

 

[FarligOpptreden]

@Necuno and dequadin:

I'm not necessarily pointing to the scenario stated by fooz, but just listing a problem in general. If you have no access to a database (iny ANY form) on a specific server at a specific host and you have a solution to implement that does not necessitate data updating, but only reading data (totaling to about 50-100 records / elements per XML file), then I think XML is a viable solution. X-path is pretty powerful and the ability to use XSLT for transformations and DTD / XSD for schema validations gives you a pretty robust and scalable solution.

Oh, and I have a LINQ-blocker add-in running in FF, deq...

 

[risingtide]

Use Firebird, free, ACID compliant and small.
mySQL was taken over by Sun, who knows what will happen with it in the future?

 

[greggpb]

There are people still using "flat" files? My word, I thought those days ended around 10 years back.

Any DBMS would be better than a flat-file, would be faster also and it's easier, no win scenario if you ask me, add into that the MySQL or even better PostgreSQL is free and there's no competition.

<troll>
Yeah wonder how google runs ? oh wait flat file database..
</troll>


For your simple needs file should be suffienct

 

[Raithlin]

{ flameSuit.Wear() }

Yeesh. This is even better than Windows/Linux/MacOS.

In the OP situation, XML is a no-no. Something as simple as MySQL (see disclaimers above about Sun), MS-SQL (Express or otherwise), Firebird (nice choice) or PostGreSQL (secure, fast, open source, not so simple ) would suffice - all databases have security by default. As long as it's not Access.

Anyone know of any sharing issues using a XML-DB?

 

[dequadin]

<troll>
Yeah wonder how google runs ? oh wait flat file database..
</troll>
For your simple needs file should be suffienct

<toll-bait>
A very special one called BigTable.
</troll-bait>

Anyone know of any sharing issues using a XML-DB?

Not specifically but Windows File Permissions always give trouble. Especially if you have a mix of domain/non-domain users on the network. I've had countless problems in the past with a network of domain computers all with local logons, and getting the permissions to work programatically sucks!

Another issues I can think of in the shared-XML-DB solution is file locking, surely two users cannot update the same shared XML file at the same time...

@FarligOpptreden cool now i don't need to put up a disclaimer when I reference it

 

[Raithlin]

Oh, and I have a LINQ-blocker add-in running in FF, deq...

Share...

 

[Raithlin]

Not specifically but Windows File Permissions always give trouble. Especially if you have a mix of domain/non-domain users on the network. I've had countless problems in the past with a network of domain computers all with local logons, and getting the permissions to work programatically sucks!

Another issues I can think of in the shared-XML-DB solution is file locking, surely two users cannot update the same shared XML file at the same time...

yeah, that's what I was thinking. @fb3k: stick to an RDBMS solution.

IMO, XML is good for inter-system-operability - and that's where it ends for me.

 

[diabolus]

...as someone mentioned, what's the problem with using SQL Server 2008 Express ? If it's really such a small app then the limits won't make any diff. It's freaking free....

http://www.microsoft.com/express/sql/default.aspx

Support for LINQ, Entity Data Model and ADO.NET Entity Framework make it easy to create next generation data-enabled applications

Limitations

SQL Server 2008 Express has the following features and limitations:

* Limited to one CPU
* Limited to 1GB of RAM
* Database size limited to 4GB
* Does not offer database mirroring
* Does not offer log shipping
* Does not allow merge publication
* Does not offer Oracle replication
* Does not provide the Database Tuning Advisor, SQL Agent or SQL Profiler
* Does not offer SQL Server Analysis Services or Advanced Analytics

Either way, i wouldn't go for XML , MySQL would still be better to work with. XML is cool for configuration/settings/temp data/web services [sending data around] , but as a full multi-user database solution..dunno......then i can ask why don't you just use an Excel Workbook? ;-)


As for Google's usage of a "flat file" system, well you can read their white paper here:

http://labs.google.com/papers/bigtable-osdi06.pdf

You'll soon realise how you can't compare this with a "flat xml file" , Google built almost a FILE SYSTEM & SERVER & API which would handle all these issues you think of when hearing "flat file db". This is totally different from dumping your XML file in a Windows Folder and expecting NTFS + Your App + Windows to do magic.

Bigtable is built on several other pieces of Google infrastructure. Bigtable uses the distributed Google File
System (GFS) [17] to store log and data files. A Bigtable cluster typically operates in a shared pool of machines
that run a wide variety of other distributed applications and Bigtable processes often share the same machines
with processes from other applications. Bigtable depends on a cluster management system for scheduling
jobs, managing resources on shared machines, dealing with machine failures, and monitoring machine status.

The Google SSTable file format is used internally to store Bigtable data. An SSTable provides a persistent,
ordered immutable map from keys to values, where both keys and values are arbitrary byte strings.

The Bigtable implementation has three major components: a library that is linked into every client, one
master server, and many tablet servers. Tablet servers can be dynamically added (or removed) from a cluster to accomodate
workloads