drill down design

Question

Post reply

drill down design

meichner

SSCertifiable

Points: 5563
More actions
October 29, 2008 at 9:47 am

#200613

I am not a DBA and I don't have a lot of experience with database design. My boss just asked me to create a drill down table or set of tables. As far as I know there is no limit to the levels that can be drilled down. I am not sure of the best way to do this. At a minimum the table should consist of a Key and a Description. I am not sure of the following:
a) The proper table design
b) What kind of constraints to put on the table or tables
b) How to Select data from this table (I assume some sort of self join)
c) If there is anything special that needs to be done to insert or update data in these table(s).
Thanks
ps. My boss has never used a relational database so I can't ask him for help.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

What's the problem you're solving? Drill down could be a lot. What is the data and how is it used/queried?

meichner SSCertifiable Points: 5563 More actions · Answer 2

We have a bunch of static data that needs to be drilled into. For instance

I have a list of Categories:

Paper

Board

Ink

When the user selects Paper from a drop down I want to display a List of Paper Types

When the user selects Board from a drop down I want to display a List of Board Types

When the user select Ink from a drop down I want to display a List of Ink Colors

When a user selects a Paper Type I want to displays a list of Ink Colors

when a user selects a Board Type I want to display a list of Companies that produce that board type

And so on and so forth.

I hope this clarifies what I need.

Thanks

DNA_DBA SSCrazy Eights Points: 9117 More actions · Answer 3

What tool are you going to use to display this data?

meichner SSCertifiable Points: 5563 More actions · Answer 4

I am writing a bunch of ASP.NET web pages to select/display the data. When a user selects one item from a drop down listbox another drop down list box will be display this new data. That new data will can then be selected and so on and so forth.

Thanks

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 5

If you are doing single lookups, meaning if I pick X, I get these choices for X, as opposed to getting less choices for x because I selected "y" earlier, I'd use one table. A column with x, the category, and a column with the choices for that cat.

MyTable

MyCat MyChoice

Color Black

Color White

Color Green

Paper Copy

Paper Cardstock

Paper XXX

Query the table using a parameter to your stored procedure (please use stored procedures) that is the cat, get back the choices.

meichner SSCertifiable Points: 5563 More actions · Answer 6

That is just what I was thinking. I do however have two questions.

1. I was considering a suggestion similar to yours except that I would have had two tables. One that contained the categories like color and paper and another table that matched what you called MyTable. I was considering this because there are times when I needed a list of categories like color and paper. I am now considering using your one table design and issuing a select distinct on the myCat column in order to obtain that list of categories. Is this a better way to go?

2. Using your wording (which is clearer then mine), had I needed to get less choices for x because I selected "y" earlier, what would my table need to look like?

Thanks so much for the help.