On 3/8/2011 5:11 PM, Darren New wrote:
> 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
>
> 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
>
> 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.
>

you can blather - it is useful info

> 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.)
>

I see - the primary key thing should prevent the 'error'.
When you state multiple times, you are implying that multiple records 
are NOT being created - just 'updating' existing records after the first 
read.


>
>> 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.
>

yes, enumerations are very friendly - I avoid using plain ol integers 
for this stuff - makes it hard to read



>
> 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.
>

Yes, there is more to it, but you do a good job of visualizing it.

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

pretty much - the owner didn't want to invest the time to make it better 
and gain the rewards.  new owner - new ideas

>> 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.
>

I think I have run into this already
select A from b where A.ID in ( select ID from C)

we got pretty complicated with poles, connections between poles, cables 
on poles and cables between poles....  a little intertwined

in its simplest form leaving out fluff like owners and such:

table poles:  (each pole)
ID, GPS Location
1 entry for each pole

table connections:  (how poles are connected)
ID, PoleID1, PoleID2, Distance
1 entry for each pole-pole joining (1 for each 'set' of cables)

table attachments:  (cable attached to poles & height)
ID, PoleID, Height
multiple entries per pole
1 for each attachment

table midspans: (lowest height between poles for each cable)
ID, AttachmentID1, AttachmentID2, Height, ConnectionID(redundant)
multiple midspans for each 'connection'
1 midspan per cable over the connection
essentially 1 midspan for each connection on a pair of poles


The rest is pretty much straight forward - objects and properties 
associated with each of the above.

As you can see, there are some circular references going on (attachments 
and midspans connect poles


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

Makes perfect sense.

To clarify my situation - I tried to present some questions that we have 
in our office without bias to get a true feel of what constitutes good 
and bad practice.

What you have presented falls in line with what I was thinking - but I 
didn't want to present it as "is it stupid to flatten" so that I would 
not bias the question.

You have presented some new ideas that 'extend' my thinking and stretch 
it.  I do not know how much will make it in to the system, but it is 
good food for thought.


We have some views in our group that state that the use of the data 
should drive how it is stored.  I agree with it to a point - mainly for 
ease of working with the data, but not at the sacrifice of usability of 
the data.  The drive is so hard that there is talk about flattening the 
database to make it 'simpler' and that adding more tables makes it more 
complex.

Additionally some of our issue is that when we finish a job for a client 
we do not need to reference the data any more.  Rarely have we found 
ourselves going back to old data.  How much effort should be put into a 
system that supports saving everything and making it accessible when one 
does not refer back to it?


Thanks for your feedback - it is very helpful.

Post a reply to this message