Ideas for logicals (index).

I'm looking for some suggestions. We're going to be creating a large
summary file and I'd like to get some ideas on what logical files I should
create. Normally, we fly by the seat of our pants and create them when
needed. But since this file is going to be large, I'd like to get them
created beforehand.

Anyway, the file unique key is by Company, Restaurant, Date, Item, Meal
Period. I believe SQL will mainly be used to retrieve the data from this
file.

I believe one logical should be created by Company and Meal Period, then
just throw in restaurant, date and item behind it. I say Company and meal
period first because currently there are only 2 possible values for company
and 4 possible values for Meal Period.

But then my concern comes in when join in other files.

For example, I may want to get region 1 restaurants only. So I'm going to
need a logical by company and restaurant, but should I throw in the other
fields too?

Then we might need to join in the Item Master file to select only
beverages. So a logical by company and item would be needed.

Finally, I think a locigal by Company, date would be good to have.

I guess I'm all over the place here, not really sure how to explain myself.
Does anyone have any suggestions? I've read for SQL indexes that you don't
want to create indexes with the same fields (but in different order)
because it doesn't really buy you anything (take longer for record to be
inserted). Would this be true for logicals too? What's the harm if we add
the other fields anyway, just in case someone in the future wants to do I/O
reads on the file or for the group by clause?

I think I've told before that you want to try to create you logicals where
the key fields with the least amount of unique values are first.

(Please no DDL solutions, boss doesn't want to start using them yet because
not too many of us have experience in them).

--

Michael Schutte
Admin Professional



Bob Evans Holiday Farmhouse Feast, Serves 6-8 l $74.99
A complete homestyle meal TO GO, ready to heat at home, serve & enjoy!
Perfect for Thanksgiving, Christmas or holiday entertaining.
For more information, visit www.FarmhouseFeast.com