Choosing a database design

[janehansen]

I am looking to build a data storage system for both spatial and non-spatial data for a small non-for-profit organization.  One idea is to develop a SQL storage system for the non-spatial data and a file geodatabase for the spatial data and relate the data using primary keys.  The other idea is to use MS Access for the non-spatial data and again a file geodatabase for the spatial data and use primary keys to relate the data. Do these choices sound reasonable or is their a better structure to use?

Thank you

Jane

[JoshuaBixby]

Looping in Geodatabase​ place.

You are asking an IT architecture-related question while providing little to no specifics, which makes it difficult for users to provide substantive feedback.

What I can suggest is, say no to involving MS Access.  I don't have anything against MS Access, I have both used it and developed against it over the years, but its use within ArcGIS is getting more and more tenuous.  There are alternatives to MS Access, but I can't suggest one because I don't know enough about your requirements.

[janehansen]

Thanks Joshua, yes I understand that I have given little specifics but you managed to answer by question.  My main concern was using MS Access which I do not like using in ArcGIS.  My situation is that I am working with a guy who is not familiar with ArcGIS.  He is working with me on this project.  His idea is to build a non-spatial storage system and me the file geodatabase and created joins between the non-spatial and spatial data within the arcgis.  MS Access is his choice for storing the data but I think there is a better method.

Whatever we do we need to store baselayers and points, lines, polygons within the geodatabase for a conservation group that will include occurrences for plants and animals.

And have a separate location that can store field data that relates to the points, lines, or polygons.

Not sure it this information help at all but I am hoping it can trigger some ideas from you.

Thanks for taking the time to reply to my post it has been of some help for sure.

Jane

[JoshuaBixby]

I don't think your situation is uncommon.  Unfortunately, I haven't found a great alternative to MS Access for situations like this one.  Personal geodatabases had/have their issues, but tying them into Access/Jet database files made them handy.  Lots of people in the business world use MS Access to provide something more than Excel for data management but without the overhead of installing SQL Server, even SQL Server Express.  The file geodatabase was a step forward in terms of storing GIS data in a file-based geodatabase, but it was also a step backwards in terms of openness and compatibility outside the ArcGIS ecosystem.

As much as I try to stay away from personal geodatabases, or even MS Access, there are certain situations where there isn't a good alternative given the requirements.  SQL Server Express is fairly powerful, but I have seen it be a bridge too far for users that can barely figure out working with MS Access.

[BirajaNayak]

Hi Jane,

As you mentioned, you have small data ( I assume it will be around 1 to 2GB  or less), you can use sqlserver express as personal geodatabase to store both spatial and non-spatial data and join. Editing sqlserver non-spatial data is easy and if your colleague knows how to modify data in MS-Access then he will figure out how to do it on sqlserver.

For your field analyst, you can make a checkout replica as file geodatabase if required and edit.

Thanks,

Biraja

[janehansen]

Thank you

Biraja

I will

Look into these options

Jane

Sent from my iPhone