SQL: the scary bit OR more realistically, welcome to the bit you don't understand yet

[Wm]

Thu, 1 Jan 2015 14:13:27 <$mi6ULaHYVpUFw2S at tarrcity.demon.co.uk>  Wm
<wm+gnc at tarrcity.demon.co.uk>

>===
>select count(*) from slots
>===
>you'll get a number, 4375 for me, don't write in saying what yours is,
>it isn't important.
>
>What *is* all that slots stuff about is the question I'll start
>answering in our next exciting instalment


===
Select
  Count(slots.slot_type),
  slots.slot_type
From
  slots
Group By
  slots.slot_type
===

should give you an idea of what is in this dumping ground, it is your
data, have a look.

aside: I'm cheating a bit in that I know the sort of fields *not* to
look at for grouping, clues are columns that have lots of numbers or
very disparate data in them aren't useful for grouping things together.
They are very useful for other analysis but not grouping and figuring
out the answer to "where is my data"

my query says slot type's 4 and 10 are what I have most of.

look for yourself, yours may be different.

something I'd like to separate from the general mess is slot type 4,
name 'notes' I can see these easily by
===
Select
  *
From
  slots
Where
  slots.slot_type In ('4') And
  slots.name = 'notes'
Order By
  slots.string_val Desc
===

gnc seems to have made thousands of blank notes, I have exactly 13 made
by a human, so

===
create or replace view Notes AS
Select
  *
From
  slots
Where
  slots.slot_type In ('4') And
  slots.name = 'notes'
===
so they didn't need to be in slots in the first place!

I'm guessing what happened is that someone figured tables were expensive
(they aren't with contemporary db's) and the gnc dev's just kept on
putting more and more data into this one table

I doubt anyone did it on purpose

by creating some views I'm making the user's data available to the user
in understandable ways, views are cheap in computing terms and allow our
devs to work towards a more sensible format for our data

hny

-- 
Wm...