I just wondering what proper (My)SQL style would be for the following situation (which I made up for the sake of this)...
You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy.
But let's say each rack has one default spice.
So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"?
In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter.
And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?
Thanks - Jim
You have a SpiceRack table and a Spice table. Each rack has many spices, in typical one-to-many fashion. So the Spice table would have rackID column. Easy.
But let's say each rack has one default spice.
So should the SpiceRack table have a column "DefaultSpiceID" or should the Spice table have a column "isDefault"?
In one sense, I would say the former, to help enforce a single default spice. In another sense, I would say having two tables point to each other is ugly and just do some extra queries to enforce that there is only one with the latter.
And while I'm at it... for that isDefault, and similar T/F columns... What's the best data type? SMALLINT (1) UNSIGNED, ENUM(0,1), or LONGBLOB?
Thanks - Jim