Tom Austin wrote:
> Interesting way of looking at building a schema - just break it down to 
> objects.  I think this fits our data very well.  I think we were doing 
> this anyway, but not on purpose - more by accident.

Trust me on this: It's the only way you ever get a schema you don't have to 
change with every software update. Record what is "out there", and to the 
extent sensible, never overwrite data. If you find yourself thinking "We'll 
need to keep copies", you've done something wrong, because what that really 
means is you need the state of something at two different times, but you 
didn't record the times.  Record *everything*, in as raw a format as you can.

I am looking at a financial system right now where I never overwrite data. 
When someone swipes a card, I put into a table exactly what the card machine 
gave me: The card machine ID, the credit card number, the amount the clerk 
typed in, and the timestamp I got the record.  I don't record the merchant 
ID in that table, or the ID of the cardholder. That all comes later, when I 
process the records.

Why? Because the merchant might change merchant accounts before telling us. 
The cardholder might lose the card. Heck, we might even accidentally create 
two cards with the same number.

I'm blathering. Nevermind.

Anyway, yes, record everything from *outside* your company in one set of 
tables, as *raw* as you can. Changes to these tables should be idempotent. 
(I.e., you *should* be able to read the CSV file the telco gave you listing 
the pole information multiple times into the same table without corruption. 
This implies no automatically-generated primary key on those tables. That 
said, again, this is just a guideline and not always possible.)

Have a second set of tables, completely created from the first set, that 
holds any calculated information that might be difficult to recalculate 
(e.g., running account balances in a financial app), but which can be 
checked against and recreated from the raw data.

Once you have stuff working, figure out where it's slow, *then* fix that. 
Usually "fix that" involves changing the declarations of some indexes, not 
rearranging the actual data (which, if you have data for which you can 
follow the rules above, you can't really rearrange anyway).

> this might be worth considering, but I don't know how easy it will be to 
> implement and keep straight.  Seems like the a user created key like 
> this could lead to duplicates - but if it is a key it will prevent 
> duplicates anyway - just prompt for another key.

Oh, yes, this isn't for user keys. I meant where in a programming language 
you'd use an enumerated type.

So if you have a "status" row for jobs that says "in negotiation", "paid for 
but waiting for someone to be assigned", "that person is working on it", and 
"ready to be delivered", you could use this sort of coding to make your 
programs more readable. "NEGO", "PAID", "WORK", "DONE" rather than 0, 1, 2, 3.

> Most of the data in question is a simple map from an integer key to a 
> piece of text.  I do have some queries on the text with joins to pull 
> data from other tables.
> 
> The text is not designed to be changed.
> 
> The alternative that we have is to flatten the relationship and put the 
> text directly in the table instead of having a key reference.
> 
> ie
> go from
> 
> tablePole        tableOwners
> Pole   OwnerID        ID    Name
> 
> 
> to
> 
> tablePole
> Pole    OwnerName
> 
> 
> 
> Any thoughts on this?

If OwnerName shows up in multiple places, then these bits would need to be 
in a PK/FK relationship to properly cascade changes.

I assume each owner has more than one pole. Hence, duplicating the name for 
every pole is inefficient space-wise and dangerous update-wise. (Especially 
if you don't use transactions.)

Is the "owner" a real independent entity? Is it rational to speculate there 
might be an owner with no poles? Or an owner who has poles but hasn't told 
you about them yet? What about an owner who just sold his last pole to pay 
for the pole he's going to buy next week?

So, yes, here, "owner" is an actual entity, not just some field about a 
pole. The name of the owner isn't associated with the pole at all.

Indeed, the *best* way to structure this would be

table Pole: GPS coords, height.  (PK: GPS)

table Owner: ID, contact name, contact phone, etc etc. (PK: ID)

table Ownership: PoleGPS, OwnerID, StartingFromTime. (PK: GPS+StartingFromTime).

table Cables: PoleGPS, cable height, fiber height, power height, 
StartingFromTime. (PK: GPS + StartingFromTime)

Later, you can go back and ask questions like "Did AT&T ever change the 
height of the power lines on the pole at Fifth and Main, or was that 
Cingular, after they bought that line from AT&T?"


*That* might be a bit too normalized for your purposes, but you'll never get 
in a situation where someone sells a bunch of poles, then comes to you and 
asks you to recreate a report from before they sold the poles or something.

See if you can look at the columns you have and come up with a breakdown 
like this into "things really happening out there". You might find yourself 
a natural at it. :-)

Now, the "groups" kind of thing I was talking about, the "secondary" set of 
tables, might be tables that take a group of poles owned by the same 
company, that constitute a single "cable run". Large stretches of cable run 
might have the cable at the same height, so your calculations could be 
faster if you had a table that put each pole in a group, and then had the 
height of the cable on the group specified, or something like that.

It's hard coming up with that sort of thing when I don't know how your 
business works, tho, but that's the approximate sort of thing I was talking 
about.


>>> We are developing a new database system to take care of some severe
>>> shortcomings of the old system.
>>
>> What are the shortcomings?
>>
> 
> Let's just say is was a single table database with about 250 columns - 
> limited only by the fact that Excel at one time only allowed about 250 
> columns.

Oh, so the shortcoming is that it was rushed and grew instead of being 
designed. Makes sense. :-)

> Putting data in and getting data out was very difficult and required the 
> sacrifice of too many brain cells.

That's exactly where SQL queries between multiple tables will help. 
Thinking in SQL instead of in terms of iterating over things is tough, but 
it's almost always possible to come up with a statement in SQL that will 
pull out all and only the data you need. If you ever get to the point where 
you're saying "for each result from *this* query, submit *that* query", 
you're almost 100% sure you're doing something wrong. In my entire career, I 
only ever did that once, and that was after I'd written it the right way and 
figured out MySql was not optimizing things in the obvious way and I didn't 
know how to make it do so.

>>> Our data has a lot of common 'choose from' items - such as the owner
>>> of the pole.
>>> The data consists of pole specific information that doesn't change
>>> (owner, height, etc) and information that will likely change (the
>>> height of cables on the pole).
>>
>> These last two should probably be in separate tables that you join at
>> runtime. (Especially if you're using MySql, where differnet "engines"
>> give you different capabilities.)
>>
> 
> That's what we are trying to figure out - how to make it make sense to 
> have them in separate tables.

Let me know if the description of tables above does or doesn't make sense.

-- 
Darren New, San Diego CA, USA (PST)
  "How did he die?"   "He got shot in the hand."
     "That was fatal?"
          "He was holding a live grenade at the time."

Post a reply to this message