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

Yes, I gathered that ;-)
Will make any transition easier as ID's don't have to be removed cold 
turkey.

>
>> 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, tho even when recognized people may have different notions of what 
it should be.

>
> Time. Cost. Quality. Pick any two.
>

That's our motto to our clients who want all three.

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

Yep, SQL does tables well - if you want objects it has to come from the 
outside.

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

I've been following it a bit over the years - at least some table 
formats in MySQL support triggers now.

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

I think I am grasping the concept.  If you need a 'pick list' you should 
have a table with the picklist values.  Storing an ID or piece of text 
doesn't matter as long as you have pk/fk set up correctly to prevent 
data corruption.

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

That's how I would approach it myself - I just wanted to hear how 
someone else would approach it.  Thanks for helping to verify my gut 
feelings.

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

Yes, that would not be smart - ID's have no meaning outside the DB.

Thanks for all your input.  You have helped to confirm that my gut 
feelings on how to set up a DB are mostly in the right directions.

2 big points I take from this:

Be willing to explore better PK/FK uses.
I have always known that a PK doesn't have to be a number, but it always 
felt foreign to use text or some other number as they don't feel as 
'precise' as an integer.  I guess I've run into too many cases of trying 
to match floats or case sensitive text to let it feel natural.

Design the DB purely from 'what is out there'.  Don't take use into 
account (or at least minimize it).


Now for a good DB engine...  I'll start a new thread for that :-)

Post a reply to this message