Tom Austin wrote:
> Sounds like a lot of 'extra' data storage, but if a DB is fast anyway, 
> then the gain of .01s on a query doesn't make a difference.

It can be. Like all things, you have to make the trade-off between 
readability, reliability, size, and speed.

> MySQL changes timestamps....  just precision or something else?

MySQL basically assumes the first timestamp is the time you last changed the 
record, and the second was the time you created the record, or something 
stupid like that. You need to read the timestamp documentation.

Basically, MySQL will change your data without telling you, depending on 
what order you declared the columns in. Yes, MySQL sucks.

> Yes, I think I am seeing it clearly.  Just store the data as it is.  be 
> smart about what is picked for the keys and use IDs sparingly.

Yep.

> IDs are great, but they can allow duplicates and potentially break your 
> DB unless your application is smart enough to prevent the error - but 
> wait, the goal of a good db is that it cannot be broken no matter what 
> accesses it.

Yep.

> IDs also require that you get the ID so that you can join on it - even 
> if you have the other data.  If you have the other data anyway, why go 
> fetch an ID...

Right. Altho the join doesn't have to use the ID.

select * from receipts,sales where receipt.time=sales.time and 
receipt.register=sales.register

works even if receipts and sales both have ID columns.

> I like the idea of building a database that prevents errors in the first 
> place.  Let's see if I can get it put into place here.  If so, I think 
> it will happen over time just because of how fast people like to work here.

Good job. :-)

> We currently have some of this.  But it a balance - is our data good 
> enough to get the client's requirements done.....  Most of the time yes.

Indeed, it's always a balance. That's something a lot of people don't manage 
to recognise for some reason.

> Yes, that's some of the push for simplifying it - but from a usability 
> standpoint.  I think the person sees how much effort I have put into 
> coding and how long some things take and wants to reduce that.  The main 
> problem is that everything was done at lightening speed so it will suck 
> by definition.

Time. Cost. Quality.  Pick any two.

>> Well, good luck with that. Some people can make that work, but I
>> strongly suspect your boss is just a jerk. :-)
>>
> 
> No, the boss isn't a jerk in this case.  We just have a bunch of young 
> people who don't know how to slow down.  Out of 6-7 people, only 2 of us 
> are over the age of 26.  If we are to keep the younger crowd, we have to 
> cater somewhat to their needs/expectations - as long as it moves us in 
> the right direction.

Fair enough. Get input from them, teach them why you're doing it the way you 
are, and leave places in the system where they can do things the way they 
like (say, the GUI) while keeping the database clean.

> Ok, so I typed it off the top of my head and referenced the wrong table :-)
> But at least you knew what it was getting at.

Yeah.

> Yes, 100% agreed - this should be avoided.  The only way I have any of 
> this at this point is by putting objects with other objects to make the 
> objects easier to work with.  I know - there is a better way of doing 
> this....

Objects inside objects isn't a problem. SQL doesn't really do objects well. 
The trick is to encapsulate that in a business layer. The bottom end of the 
business layer is the database table definitions, and the top end is the 
conceptual things your business thinks about.

> Yes, triggers, I remember those back from my SQL Server days.  They can 
> do a lot for you - more than just keeping your data straight.  I can't 
> wait to get back to those and be able to use them as a tool :-)

I don't think MySQL has triggers.  Heck, you have to go out of your way to 
get MySQL to enforce foreign key constraints, let along triggers or views.

> yes, I do have to 'fetch' the ID from time to time.  I like not having 
> to do that.  It's just getting things straight enough so that not using 
> an ID doesn't mess me up too bad.  I don't have as much experience with 
> setting up keys and making sure they don't mes things up.  Bu that is 
> something that I am going to be working on :-)

Cool. And again, it's mostly just a personal preference of mine borne out by 
experience. It's rejecting the concept that every table, by default, must 
have and be referenced by an arbitrary integer key.  It's not saying they're 
bad, but just "think about it for more than 2 seconds."

> I think for the time being we will be morphing with each job until 
> things get more stable.  Then we may start putting the data more together.

Yeah, keep it agile. Keep every version of your schema around so you can 
tell what you changed.

> Well, there is - transformers and cables that are perpendicular to our 
> direction of travel :-)

Oh, fair enough. But you see what I'm trying to say.  If, logically, you 
can't have one without the other, they need to both be in the same row.

> So on lists like this, an ID is a good idea - and you wouldn't use the 
> ColorName as the pk/fk.

Not necessarily. Either an integer or the color name itself would work. This 
table is likely not to change often enough to be at risk of being screwed 
up, and honestly color names are already pretty arbitrary.

If you had a table of color RGB values, that would be different. Then you're 
talking about something "real". But the names themselves are already just 
symbols for the actual color, and one color can have multiple names etc.

> One can take the ideas of using non ID columns for pks too far.  So, in 
> the case of colors, go ahead and set up a pk/fk relationship even if it 
> is the *only* thing in the second table.

Yes. The point there is to enforce that all the colors are selected from a 
fixed list of possible colors. The text isn't really text, it's an 
identifier for a color in textual form. If you store it multiple times in 
textual form, it gives an opportunity to accidentally get a piece of text 
that isn't a color identifier into the color column.

Now, if you wanted to map colors to unique-names-of-colors (for PANTONE or 
something, say), I'd say make the RGB of the color be the PK.


>> You can certainly start out with the color in its own column, then move
>> things around later when you find it's problematic. You might have some
>> processing time, taking the system offline for half an hour while you
>> rearrange the tables and such, but I'm guessing (given you were using
>> Access before) that this wouldn't be a problem every month or two. :-)
>>
> 
> So, if we see it probably needing to be 'broken out' in the future, we 
> should go ahead and break it out now.

I would write code that accesses the database while being aware that it 
might need to be broken out. I wouldn't necessarily break it out now. You 
can break it out later, but only if you don't have a big rewrite to break it 
out.

Figuring out where the fracture lines lie is experience.

>> You can even start out with the color as text in the column, later add
>> another table that has the colors as the primary key and add the FK
>> constraint, and after that start putting the different translations or
>> families or whatever on that second column. I.e., where I refered to the
>> "color.ID" above, you could just use the text of the color name as the
>> ID, then have the translation or the family or whatever keyed off of
>> that. Only after space or speed becomes a problem do you really need to
>> worry about making it into an integer ID or some such.
>>
> 
> With what little you know about what we are trying to do, what approach 
> would you do in this case?  Remember, you know you may want to do 
> something with color families, but you don't know yet.

If you have a fixed number of colors (i.e., if you're talking about using 
color as a label rather than an *actual* color) then I would put the colors 
in a table with an ID and a color label and refer to the ID and not worry 
about the families and such.

I.e., if you're talking about "this is a green tag pole, that is an orange 
cable, this is a blue-white terminal" then the colors are tags. If you care 
about which shade of orange it is because you're mixing matching paint or 
something, that's not using the color as a label so I'd have a different answer.

So I would start with the colors in a table with
   ID int, ColorName varchar(50), primary key ID, unique index ColorName.

Use the ID to refer to colors, and you'll have the most flexibility in the 
future. Don't store IDs outside the database, tho - always translate them to 
color names before sticking them in external files or hard-coding them into 
a UI or something.

> I agree, it seems that the only way we would need a beast of a machine 
> is if we somehow make it very inefficient..... but that's a trade off - 
> development time or hardware cost.....

Yeah, where a "beast" machine is $1000. :-)

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