Sign up ×
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.

Currently the database contains something like so (these are the main fields but there's a large amount more):

| Agency   |     parent     | main_term
|        1 |  Office Staff  | Supplies |
|        1 |  Supplies      | Postings |
|        1 |  Supplies      | Artwork  |

The program would spit this data in a list like so, looking at the parent and main_term for proper indentation:

Agency #1:
Office Staff

Right now the data seems redundant and creating a program for average users to edit or restructure the lists would turn out very confusing.

Is there a better way to structure this using multiple tables?

Thanks in advance.

share|improve this question
Which DBMS are you using? Postgres? Oracle? This is quite easy with a recursive query. – a_horse_with_no_name Aug 7 '13 at 14:34
Hey a_horse_with_no_name, I'm using Access. – foulbox Aug 7 '13 at 14:36
No dice with MS-ACCESS, you will need to write a (VisualBasic) program for that. – a_horse_with_no_name Aug 7 '13 at 14:42
I was looking more for guidance on restructuring the database. The way it's set up right now, would you say it was up to standard? Is there a way to make the data easier to manage? – foulbox Aug 7 '13 at 15:38
If you have 50 items under supplies, are you really going to repeat Supplies 50 times in the table? Probably a lot more efficient to use surrogate keys for those instead of repeating the name so many times... – Aaron Bertrand Aug 7 '13 at 15:39

Your Answer


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

Browse other questions tagged or ask your own question.