Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server or PostgreSQL. Right now, the table structure is a mess. Instead of properly establishing relationships between tables, multiple fields and parameters are cramped in a single table.

I have broken up the table and plan to regroup the parameters into separate individual tables and link them together. My question is, the unorganized tables contain many rows of data, with over 40 different parameters, what is the best way to break up the table and still have all the data repopulated in the new tables linked?

share|improve this question
    
Mods please disregard the flag. I initially thought this should be on DBA.SE and flagged it, but it's really a Microsoft Access question not a database/PostgreSQL question. alchuang: Sorry the first response isn't really about your question. I found this because it's tagged PostgreSQL and I watch PostgreSQL questions, but I can't really help with MS Access. –  Craig Ringer Mar 11 '13 at 22:45
    
The question also involves moving the data from Access to SQL or Postgres so I do think it might be better on dba.se. As it is it's a pretty general question about relational structure. I'm not sure it can be answered without more specific information. –  Brad Patton Mar 12 '13 at 1:28
add comment

migrated from superuser.com Mar 12 '13 at 6:29

This question came from our site for computer enthusiasts and power users.

1 Answer

up vote 2 down vote accepted

I think your best bet is to do the restructuring in Access. After you're confident everything works, you can

  • create tables and constraints in PostgreSQL,
  • export tables from Access as CSV files, and
  • load the CSV files into PostgreSQL.

Everything works includes application code.

Make sure your Access database is backed up, copied, and not in use before you make structural changes.

To restructure tables in Access,

  • select distinct rows into a new table ("make table" query), or
  • create views (saved queries) that select the right rows and columns, or
  • copy a table, open it in design view, and delete irrelevant columns. (You'll probably want to delete some columns from the original table, too.)

I'm pretty sure Access lets you export the results of a query, so you can build queries that have the right structure, then export the queries.

share|improve this answer
    
Catcall thanks! What about the current data within existing tables. Should I create empty new tables first and then manually copy the data over or how do I do that? –  alchuang Mar 13 '13 at 19:41
    
@alchuang: The first option above always works: "select distinct rows into a new table". That is, it always works on the data. It doesn't capture the constraints in the original base table. I prefer to create new, empty tables, then select distinct rows into them. (As an insert query, not as a "make table" query.) –  Mike Sherrill 'Cat Recall' Mar 13 '13 at 20:27
    
Thanks! So by selecting distinct rows and inserting them the original constraints are kept within the new table? I also have a question regarding the second method - "create views (saved queries) that select the right rows and columns." How are queries different from tables in terms of data migration to PostGreSQL? Are exported tables and exported queries both as effective? Are there advantages of one over the other? –  alchuang Mar 14 '13 at 14:32
    
Inserting distinct rows has no effect on the structure of tables. That includes column definitions, foreign key constraints, other constraints, etc. Queries and tables are the same as far as exporting data to CSV or tab-delimited files. Queries might have a slight advantage; exporting a query gives you the most up-to-date data. But queries are based on the old base tables. If the constraints in those tables let bad data in, you'll get bad data in the queries. So you'll export bad data from the queries. –  Mike Sherrill 'Cat Recall' Mar 14 '13 at 14:42
    
Thank you for the prompt reply! Does Appending do the same as an insert query in Access? If so, I think I'll be going that route. Creating empty tables with defined fields and constraints and then querying the data from the old database and appending them over to the new fields. Does that sound like the right way to do this? –  alchuang Mar 14 '13 at 15:18
show 1 more comment

Your Answer

 
discard

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.