I have to create a database with information on Scottish buildings from 17 and 18 centuries. In order for my data to be searchable I need to have a series of tables connected with 1 to many relationships. Each building must be positioned on a map, which will have other layers like geo-referenced historic maps, and other features, like industries that supply materials needed for the building's conservation, etc... The database will be continuously used and updated. I was using Access, but I found that the connection with ArcGIS was not ideal: either I had to export from Access and Import in ArcGIS a single table or query, or I had to create an OLE DB connection, which isn't able to "read" the relations between the tables. Therefore I thought doing all the database directly in ArcGIS would be better. I created different tables connected with realtionship classes, and one of them has X Y coordinates that give me the position of the buildings. The problem is I can't query more than one table at a time, nor create a report from more than one table at a time. what am I doing wrong? I'm new to databases and to both Access and ArcGIS, so any suggestions are welcome. Thank you.
Based on the info you provided, it's tough to fully understand your requirements surrounding the maintenance and consumption of the data. For example, are you planning to make updates in ArcMap only? Will the data be viewed from an end-user standpoint in ArcMap or will the data need to be published as a map service and visible to others via the web? For the time being, in reviewing what you wrote I'll keep it simple and assume you're looking for an ArcMap-only solution at the moment. The project you described could take so many different paths! It sounds interesting though!
Are you planning to use ArcSDE with either SQL Server or Oracle, or are you planning to use something like a file geodatabase? I agree with you the Access is not the best candidate, so I would avoid the personal geodatabase (plus I don't think it's supported at 10.2). Generally, the file geodatabase will suffice if you or only a small handful of people will be making edits at separate times. How much data are we talking about (i.e., small like 50 MB, or much larger like 10 GB)? The biggest thing that I see you needing to think about, at least at the beginning, is how to structure your tables and their various relationship classes. If you haven't already, you'll have to give some serious thought about how the data model is going to look. That includes a number of things like primary keys, foreign keys, constraints, field lengths, data types, general cardinality, and unique ID formats among other things.
If you choose to go with a file geodatabase to store your various tables and "link" them with relationship classes, that should work fine. I would suggest reading about the various relationship class types to understand your options. That being said, use your X and Y columns as the source for using the "Add XY Event Layer" tool in ArcMap. Once the features appear on the map, identify one of them to see how the related information shows up.
In terms of creating reports, what type of reporting engine are you thinking of using? There are several open-source options and there are several off-the-shelf options too. Tableau and Crystal Reports are two semi-popular reporting engines to investigate.
Thank you for your reply. I'm sorry I wasn't very precise in my explanations, but that's because I'm still a little bit confused about all this. I'm using ArcGIS 10.1. The data will be used by two people for the moment, and the idea is to publish it in a map service at some point, but not for now. I was using a personal geodatabase, because I was still uncertain if to use Access or not, but if you say a file geodatabase is better, I'll try that. The amount of data is small for now, but it will get bigger and bigger, because eventually a lot of pictures and drawings will be added as well. In any case, I have a structure for the tables which should work, since I was using it in Access, the only difference is in the type of relationship classes, but I've already looked at that (hopefully). My main problem is creating a report from multiple tables. Unfortunately, if I understood right, Tableau and Crystal Reports are no longer included in Arcmap in the version 10.1...