1

I have developed a C# front end app for management of an SQL Database for the purposes of document control. The project this database is for has several consultants and a client spread in a variety of geographic locations. Security concerns mean that probably finding an internet connected database will not be a good solution. I am looking for a way to provide the ability for the client and other consultants to have access to the data, for browsing purposes more than anything, but am interested in perhaps being able to sync changes made by others back into the main database down the road.

The firm I am with is an Architecture firm and we use Revit for our modeling and production of drawings. Revit is interesting as an application because the documents are used as relational databases. So it occurred to me that perhaps I can have my application create a document that is really a relational database, and can provide an application to read and manipulate the data, and provide the data as a document, to the other players. Then they won't need access to the actual SQL Server database, but can see the data as it was when the "data document" was created.

Then I considered serialization. Basically, if I just write a serialized form of the data from my application to a document, and then provide a similar application that simply populates the model from the document rather than the database, it accomplishes the same thing. Since I am using ObservableCollections, Linq, and MVVM practices, I can even reuse much of the code between the two applications.

So what are the advantages with the portable relational databases that I would lose with the serialized data? And does anyone know how Revit manages to do this? I don't think it is an SQLLite or NoSQL solution, as I don't see any of the databases associated with that installed on the Revit machines.

Paul Gibson
  • 115
  • 7
  • See [Why would I use a database instead of just saving data to the disk directly](http://programmers.stackexchange.com/questions/190482/why-use-a-database-instead-of-just-saving-your-data-to-disk) for the reasons why. – Robert Harvey Mar 02 '15 at 16:11
  • Thanks for that link . . . some good advise. I am still scratching my head though, because Here is Revit, giving me the ability to have an encapsulated document that is a fully relational database. I think it must use SQL Server Express somehow but I can't figure out how it manages to do it. – Paul Gibson Mar 02 '15 at 16:40
  • You can install the free version of SQL Server on a desktop and do any necessary synchronization. – JeffO Mar 02 '15 at 19:46
  • @JeffO I agree, but still don't see how that gets a document that encapsulates a fully relational database (the way Revit does it). I can export from Revit in to SQL Server (and in fact do this for other purposes), but the model file (a .rvt document) somehow also does this itself. – Paul Gibson Mar 02 '15 at 20:20

1 Answers1

2

I do not know Revit, but MS Access provides this functionality (treating documents as relational data) for more than 20 years. The main advantages over serialization are

  • external data: you can retrieve and manipulate data in portions, you do not have to load 1GB of data to change just one single character in a single record.

  • parallel access by different processes.

  • indexing capabilities (you can simulate this using in-memory dictionaries, but that might be getting more complex than indexing provided by a database)

  • meta data: every relational DB, even lightweight ones, allow you to define a database schema, and query it also. In case of MS Access, for example, you can use the Access front end application and inspect the DB schema, define datatypes, add a description to each field, add a graphical schema, and so on.

  • depending one the db system, you get some goodies like views or constraints.

I think most of these points are also valid for SQLite.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
  • I had not thought of the data portions . . . but even for a large project this database is relatively small. 100K documents in it is not much bigger than 1MB. But I am not sure of the language . . . is a "document database" different than an "embedded database"? – Paul Gibson Mar 02 '15 at 15:41
  • Um, how "portable" is MSAccess? SQLite seems far superior in this regard. – Robert Harvey Mar 02 '15 at 16:11
  • @RobertHarvey: ;-) of course, but that is obviously not the meaning of "portable" the OP had in mind. – Doc Brown Mar 02 '15 at 16:16
  • Oh, I see. Like a document format. I'm in. MSAccess is probably as good or better than anything else; you could even embed reports in it, if you wanted to. – Robert Harvey Mar 02 '15 at 16:17
  • @PaulGibson: even reading and writing just 1MB can become slow when you have to do this often. – Doc Brown Mar 02 '15 at 16:22
  • Thanks Doc. I agree . . . am just really wondering how Revit manages to have both things. A document which is a relational database (I know this because the revit API gives the ability to do LINQ type queries on the documents database). How is this possible? SQLite is embedded, so there is no document that I have found in my testing. – Paul Gibson Mar 02 '15 at 16:44
  • @PaulGibson: it seems you are mixing some things up. SQLite beeing embedded means the SQLite libraries are linked directly into your program, and there are no separate processes running. This has nothing to do with the place where the actual SQLite databases are stored. Those are typically single files, though you can use SQLite as an in-memory DB. – Doc Brown Mar 02 '15 at 20:41
  • Doc, I am sure I am mixing things up . . . I will play with SQLite more, as this may be exactly what I need. Thanks! – Paul Gibson Mar 02 '15 at 21:19
  • And sure enough here is a good explanation of it for SQLite: [https://www.sqlite.org/appfileformat.html](https://www.sqlite.org/appfileformat.html). This does seem to meet the Portability requirement that I am after. Thanks again Doc. – Paul Gibson Mar 02 '15 at 21:23
  • @PaulGibson: if you want to understand how programs like SQLite use a file as a database, this part of the docs may be of use: https://www.sqlite.org/atomiccommit.html – Doc Brown Mar 02 '15 at 22:00