> I have a general database design question for you...
> Suppose, I have a database of entities, that have two attributes, say,
> location and name. Location is numerical and name is a varchar.
> I need to be able to search by both location and name (e.g. get all the
> entries whose location is 1 and name is John etc...).
> It is simple so far - I'd just create an an index on both location and
> name...
>
I am not quite understanding why a multi-column index won't work. Can you
explain a little more?
Here is what I was thinking:
create table entity(
type int,
location int,
name varchar(100) not null,
);
create unique index my_index on entity(location,name);
It seems as though an "and" query would run quickly. It also seems as though
if you needed to get a few records that were one of several names, a query
such as the following would run quickly:
select * from entity where location = 5 and (name = 'joe' or name='joseph');
or are the aliases a seperate attribute? I am not clear on exactly where the
alias comes in.
Regards,
Jeff
