current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite

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
--Supplies
----Postings
----Artwork

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
1  
Hey a_horse_with_no_name, I'm using Access. –  foulbox Aug 7 '13 at 14:36
3  
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
1  
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

 
discard

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

Browse other questions tagged or ask your own question.