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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.