|
![](/i/fill.gif) |
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
|
![](/i/fill.gif) |