Postgresql – How to Reorganize Microsoft Access Table

postgresql

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?

Best Answer

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.

Related Solutions

PostgreSQL – Should Large Tables Be Split for Optimization?

I think, this is a design question rather database technology. So, I provided the answer with what DB technology am current working on. But, you prefer to have it explained using pseudo, so here you go with another answer. I could go and edit the other one, am intentionally leaving as it is so that if SQL Server guy visits this page then he may appreciate it, we never know!

Table Name: User
Reason: Requirement is to always associate the primary photo with the user it make sense to store just the reference of Primary Photo Id along with User record.

Columns
----------------
UserId (PK)
PrimaryPhotoId (FK to Photo table)

Table Name: Photo
Description: It would be lot easier to manage just single table. It is definitely possible to extend columns in this table in future just in-case if you want to store more information like tool-tip, Alt text, image description, etc.. The other tables doesn't get disturbed and requires absolutely no changes.

Columns
----------------
PhotoId (PK)

Table Name: UserPhoto
Description: This table provides you the flexibility to have one to many relationship. It also identifies the record whether every Photo is private or public, using the flag (IsPrivate column). If you prefer to restrict that every user shouldn't associate the same Photo twice then the proposed composite Unique Key make sense, otherwise you need to extend the unique key but the table design should remain the same.

Columns
----------------
UserPhotoId (PK, AutoIncrement)
UserId (FK to User table)       - Composite Unique Key
PhotoId (FK to Photo table)     - Composite Unique Key
IsPrivate (Bit Data type to store 0 or 1. 0 value represents for 'Public', 1 value represents for 'Private' photo)

PostgreSQL – Performance Issues During Count with Index on Pivot Table Key

I would begin with a change like this:

select cards cards, count(*) count 
from aggregated_hands ahs
where exists
    (select *
     from aggregated_hand_preflop_action apa
     where ahs.id = apa.aggregated_hand_id 
     and apa.preflop_action_id = 1)
and tag_name = 'reg'
and effective between 1 and 25
group by cards;

But it also can depend on how sparse the 1's are on the aggregated_hand_preflop_action table.

measure, then add this index:

create index on aggregated_hands (cards) where tag_name = 'reg' and effective between 1 and 25;

If that index doesn't show up in your explain or you are not gaining any speed from it, get rid of it.

Best Answer

Related Solutions

PostgreSQL – Should Large Tables Be Split for Optimization?

PostgreSQL – Performance Issues During Count with Index on Pivot Table Key

Related Question