[thelist] (no subject)

[Palyne Gaenir]

Hi Jay,

> On 12 Jul 2000, at 12:50, Jay Turley wrote: My team was just told
> that the ratio of indexes to information in our database design
> was too high. 

LOL.  An interesting way to put it.  Does that mean, instead of 
saying, 'we've reviewed your design and there are many improvements 
we can recommend', someone is saying, 'according to this book....' 

> Can anyone help me figure out what this means in the
> "real" scheme of things. 

They think you have too many indexes, of course.  What did I miss 
about that question, it couldn't have been that obvious... the way 
you put that... I would assume you mean literal INDEXES, meaning, 
indexes set in your database, on a given table, which orders the 
table in a specific way (e.g., by 'last name' column) so that actions 
performed on the table run more quickly (e.g., it's faster to find 
something by last name if the table itself is ordered by last name, 
for the same general reason it's easier to find stuff when you file 
it alphabetically than leave it in a huge random pile on your desk-
sized in-box).  Of course, if you have an auto-numbering field of any 
kind (IDENTITY type in SQL Server 7 which you said you use) that is a 
form of index.

> We feel that the design is correct, but we do have a number of
> tables that only have 3-5 fields, one of which being an ID which is
> kept in a field in our main "person" table. 

Gee, that's not many fields for a table.  Do you have actual indexes 
set on these tables?  Unless you've got a bazillion records in them I 
wouldn't think they would need an index.

> How does the ratio of indexes to information impact dB performance?

Well it really depends somewhat on the situation.  If your query 
needs to look into four tables indexed by auto-numbering instead of 
one table indexed the same way, it will take longer.  If you index by 
value X but you are often querying the table by value Y, maybe you 
should either make a Y index or simply leave off the X index.  On 
tables with that few fields, unless they are ALL varchar(4000) fields 
and all full with lots of records and you're doing crazy searching 
without a 'real' searcher (like Verity)... well then I just can't see 
why you'd specifically need to set indexes on tables like that, but 
what do I know.

> How can I justify not moving 7 or 8 tables with 3-5 fields each
> into the main table (giving it an additional 15-30 fields) other
> than on the fact that we have 20 odd hourse invested in code
> already? 

You could always make a View.  A view's actually sort of system-side 
query.  You can put the data into one view, and then query the view 
instead of the 8 different tables.  I'm NOT saying this is the best 
way to go, I really don't know in your situation.  I'm just saying 
that if someone is bitching because they think your system is too 
complex, and you know what you're doing and it's working, then maybe 
making them a view and saying, "Here, here is ONE table, it has ALL 
the data, be happy" will ease things somewhat, without requiring 
redesign of your tables.  (Of course... if that would work, then it 
might be just as logical to make ONE new table and pull all your data 
into it... still a separate table from the 'big one'.)

The larger a table, the more data in it, obviously the slightly 
longer it might take for actions upon it.  That's one reason to 
suggest they not add up to 30 new fields to it (and that's a lot -- 
sounds like you guys have both extremes :-)).

Here's a management tip: when arguing a point you don't really 
understand and/or probably can't win otherwise, find the numbers.  
Look at the max number of fields you might have to add; the max 
number of characters that could entail for one record; and the max 
number of records that could involve over say, a year's time.  How 
many bytes would that be?  Now calmly and worriedly suggest that the 
potential of adding that MANY bytes to their original data table 
(this could reach in the billions easily :-)) could definitely impede 
its performance, potentially causing, in the future, the need to 
split apart the data in the table to more than one table.  You want 
to consider the future implications of your present work of course.  
This would not only waste the development money and time spent thus 
far by your team, but could potentially require a good deal more down 
the road.  Tell them, in the interest of "a more modular" system 
which will not only work with the company's current needs but be more 
flexible for its future needs, you support the separation of the 
data.  (Offer the view(s) or 1-3 'consolidated' tables if all else 
fails, as a compromise.)  (Mgmt: Expert BS.  You're welcome.)

> Additionally, we just discovered that one of our associated tables
> consists of information already in the company in another database
> on another machine. The DBAs want us to use that table rather than
> replicating it in our database. 

This is a good suggestion.

> Can SQLServer work across databases like this? 

Certainly.  If they do not give you direct permissions to their 
datasource (which they may not want to), (a) they might be able to 
either make a 'view' of their table which you can work with or (b) 
you might be able to use LDAP to access the info.  They're right on 
that one; there is seldom a good reason to duplicate information.  
That's one of the good reasons for making a database to begin with, 
to avoid that.

FWIW, I am not a db expert.  I've just had to do too damn many of 
them in this life in order to do OTHER stuff. 

Regards,
Palyne