Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

For various reasons I understand it is poor practice to rely upon OBJECTID fields to relate different tables together using a relationship class.

Therefore, I have a series of several tables related through RelationshipClasses on a user-defined primary key field. I plan to implement code later that will auto-fill this field upon importing a new record.

When I import new records to these tables, I need to import from CSV files and maintain the integrity of the foreign key relationships already defined in these CSVs. How can I accomplish this and still assign the newly imported fields new primary key values in my custom defined field?

This database will be used to manage traffic counts. See part of the schema below. For example, I have two CSVs of records to be inserted into different tables: counts.csv and intervals.csv. Counts.csv contains general count information (ID of location where they were collected, Date, weather conditions comment). Intervals.csv contains the specifics of each count (one row for each five-minute interval of count data collected). In counts.csv, before import the Count ID column will be numbered 1-n, and the Count ID foreign key in intervals.csv will reference those same numbers, 1-n.

If I append counts.csv to the existing geodatabase table, they will receive new values in my user-defined custom primary key field. So, the intervals will no longer point to the correct count. How can I re-point each interval to its appropriate parent count?

Thanks

Database entities:

Locations (Point Feature Class):
--------------------------------
- Location ID
- Text description

Counts (Geodatabase Table):
---------------------------
- Count ID
- Date
- Comment
- Weather conditions
- Location ID (Foreign key to location)

Count Interval (Geodatabase Table):
-----------------------------------
- Interval ID
- Start Time
- End Time
- Volume of traffic
- Count ID (foreign key to counts)
share|improve this question
2  
Can you provide an example of the data and/or a diagram of the relationships? –  blah238 Sep 20 '13 at 4:19
    
The basic idea is that we have intersections stored as a point layer feature class and traffic counts at each intersection. (Side note: traffic counts themselves are stored in five different tables tied together with a set of RelationshipClasses.) I need to be able to upload a list of intersections and multiple traffic counts at each intersection all at once. The traffic counts come in in five different CSVs (one for each table), and records in the CSVs reference each other through an ID field and reference the ID of the intersection points in the feature class. Does this give enough info? –  user1100652 Sep 20 '13 at 6:05
2  
Please do not use the comments section to clarify your question. Edit your question instead to add the relevant information. There is very limited space and formatting capabilities in the comments box. Lastly, a visual aid and sample data would really help more than a verbal description. –  blah238 Sep 20 '13 at 6:06
    
Is the data static, or are you creating a query for ongoing? I would rationalise the data structure based on your description..... –  Willy Sep 20 '13 at 6:18
1  
Sorry--was not aware I could edit my question. Much easier! –  user1100652 Sep 20 '13 at 6:28
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.