MySQL table design style with foreign keys

[JGH]

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

 

[sleipnir214]

Since the default spice in a particular rack is a property of the rack, not the spice, the SpiceRack table should have a DefaultSpiceID column.

Also, you've said "each rack", from which I infer there are more than one rack. You can't enforce a default spice for a rack by adding a True/False isDefault field to a spice. How are you going to know for which rack a particular spice is the default? The only solution would be to have a column in each row of Spice named something like "isTheDefaultSpiceForWhichTable", which will hold the ID of the rack for which a spice is the default. If you're going to go through all that, I point you back to my previous paragraph.


Where do the foreign keys come in? You haven't described anything that requires a foreign key. Remember, a foreign key is a constraint -- it constrains the possible values in one table column to match a value in another table's column. If you have an optional default spice for a rack, and a rack has no default, then unless you add an entry for "No Spice" in the Spice table, you can't use a foreign key at all on the DefaultSpiceID column of SpiceTable -- there must be a matching value. By not having the foreign key constraint, you can enter a value of zero (assuming Spice IDs begin with 1) into DefaultSpiceID to designate that the rack does not have a default spice at all -- without having to add a bogus entry into the Spice table. That bogus entry will mean you have to program in exceptions into all your code to use this database.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[JGH]

Thanks sleipnir. I think I haven't asked the best question...

The Spice table, as noted, would have a rackID column, so we know to which racks each spice belongs. So if a spice is set to isDefault, it would be the default to whichever rack its rackID points to.

I probably wasn't clear enough. The reason I brought up foreign keys (which I have very limited knowledge of... as you can see) is the Spice table already has one where Spice.rackID=SpiceRack.ID. Having a defaultspiceID column would create a foreign key of SpiceRack.DefaultSpiceID=Spice.ID. Right? So that means both tables are pointing at each other.

I guess it's imperfect to maintain either way, but it seemed like it would be more cumbersome where the tables reference each other. Anyway, I am still on MySQL 2.23, which does no foreign key enforcement, so my reference to foreign keys just meant that the columns point to other tables' IDs, not that MySQL enforced any constraints.

Thanks - Jim

 

[sleipnir214]

Pointing at each other" is something of a misnomer. Unlike database engines like Access, relationships between tables are mostly notional. You have a Spice table and a SpiceRack table, and the two are not related except in a query.

The only relationship you formally define within the database in MySQL is a foreign key. The MySQL database engine is told to make sure that a value in column A of table B must always match a preexisting value in column C of table D. This is done solely to maintain data consistency in a database. Read up on MySQL foreign keys and how they work here:

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

No matter where you record the relationship between a Spice and a Rack, multiple things must happen. If you use an isDefault in Spice, when you make coriander the default spice for rack 1, that the existing defaul for that rack is unset. If you use a defaultSpice in rack, you'll proably want to make sure that coriander is actually in rack 1 when you try to make it the default. Either one is going to have to be handled in code.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

You have a Spice table and a SpiceRack table, and the two are not related except in a query

actually, two tables can be related, via primary/foreign keys


i just didn't want anybody ever to see your statement and take it out of context

but you guys are beating the wrong horse

the original statement:

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.

perhaps easy, but wrong


if each rack has many spices in "typical one-to-many fashion" with FK rackID in spice referencing PK id in spicerack, then the primary key of oregano in spicerack 1 *must* be different from the primary key of oregano in spicerack 2

which, sue me if i'm wrong, doesn't sound right, never mind any consideration of "isDefault" for the time being

this isn't a one-to-many relationship, it's a many-to-many relationship, and should be designed as such

spice: id, name
spicerack: id, number
spicerackspice: spice_id FK, spicerack_id FK

once you've done this, it's quite a simple matter to add the "default spice for this rack" --

spicerackspice: spice_id FK, spicerack_id FK, isDefault

no need to check whether it exists first before it can be the default, because it is made the default either at the time (in INSERT) or after (in UPDATE) it is created







r937.com | rudy.ca

 

[sleipnir214]

actually, two tables can be related, via primary/foreign keys

Huh?

A primary key doesn't relate one table to another -- a primary key is a unique index within a single table.

And a foreign key merely says that the pool of possible values for one column must be selected from the pre-existing values from a column in another table. It may describe a vague connections between the tables, but it does not relate the records of one table to the records of another. If this schema had a table ContainerTypes (with records for "glass bottle", "metal can", etc.) and if Spice had a column containerID which also had a foreign key constraint to a column in ContainerTypes, for example....

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

are you trying to lead me on, slep ole buddy? i'm convinced you know this stuff

of course the primary key does not relate anything -- that's the job of the foreign key, but the foreign key can't relate to anything unless it has a primary key to relate to

and if you don't think that's a relationship, try declaring ON DELETE CASCADE and see what happens when you delete a parent row

i know you know this stuff

i say again -- primary/foreign keys do define relationships, and they are more than "vague connections between the tables"

or are you saying that for certain table types in mysql the enforcement of relational integrity is not even supported?

well, yeah, we know that, but if you want relational integrity, you have to use the right table type

and what did you think of my many-to-many schema? and the practicality of the isDefault attribute in the linking table?

r937.com | rudy.ca

 

[JGH]

Okay, I think we're overanalyzing the spices (and getting off topic, but at least it's educational .

My question was about where to declare the default value. It doesn't have to be spices in a spice rack. It could be people in a family household, where it is a one-to-many relationship. (We will assume that no one has a clone, leads a double life with another family, or has a namesake in another family).

Other than primary keys don't relate anything, what is the consensus among the SQL gurus?

Thanks - Jim

 

[towerbase]

Perhaps the spice rack was not a good example for this discussion?

Although JGH says it is a one to many relationship, in reality (or in my kitchen at least), oregano can appear in more than one rack. I am not sure I understand the idea of a "default spice" either.

Would countries and cities provide a more concrete (sic) example of a one to many relationship with the property of "capital city" being the equivalent of "default spice".

Country Table: countryId, countryName.

City Table: cityId, cityName, countryId.

The question then becomes how should capital city be represented. Is it a property of the country or is it a property of the city or is a third table Capital required which is

Capital Table: countryId, cityId.

Andrew
Hampshire, UK

 

[sleipnir214]

r937:
A foreign key does not define a relationship. Assume that we have a table which records information about the instance of a t-shirt. One of the columns in the table TShirt will be "size". Another table, "Sizes", lists all possible t-shirt sizes that are available: "S", "M", "L", "XL", "XXL". If TShirt's "size" column has a foreign key which points to the appropriate column in "Sizes", there is no relationship defined -- there is only a constraint to the possible values that TShirt's "size" column may take.

It's not a relationship, it's a filter of possible values. We may be simply having conceptual differences, but I have found over the last decade and a half that thinking of foreign keys as constraints and not definitions of relationships prevents more conceptual bottlenecks.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

sleip, i'm happy for you! everybody else in the world sees an actual relationship when a primary/foreign reference is made, but if you only see a constraint, i ain't gonna stop ya

and you never did answr my question about what happens in ON DELETE CASCADE -- how do you explain the automatic deletion of the child row if it's only "a filter of possible values"?

yeah, i guess we'll have to agree to have "conceptual differences"



r937.com | rudy.ca

 

[sleipnir214]

How do you explain the automatic deletion of the child row if it's only "a filter of possible values"?

That is simply automatic removal of all records that have values that no longer pass the new filter definition.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

you'd be wonderful at defining stuff like phlogiston

what would define a relationship in your world?

r937.com | rudy.ca

 

[sleipnir214]

you'd be wonderful at defining stuff like phlogiston

Cute. Specious, but cute.

what would define a relationship in your world?

Mathematically speaking, a relationship is what we call a table: a set of tuples.

Practially, and in terms of MySQL, any SQL query which uses the JOIN clause (or its functional equivalent) combines, for the time that the query is run, two tables into a single set of tuples.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

no, dude, a relationship is not a table

a relation may be implemented as a table, but we weren't talking about relations

and if you want to play the dbdebumph card, i will laugh at you every time from now on you use a NULL in one of your queries, because relations don't have NULLs, do they



r937.com | rudy.ca

 

[sleipnir214]

r937:
Have I done something to offend you? Is your identity tied up tightly into this question or something?

Did I not answer the question, "what would define a relationship in your world?"?

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[JGH]

Guys guys guys... Settle this outside in the back alley.

Has the Expert SQL Council reached a consensus on which table should store the default value for a one-to-many table relationship (if it is, indeed, a relationship)?

Is it the SpiceRack table, the Spice table (for which there are no duplicate spices, ok?), or a third table, DefaultSpice (columns: id, spiceid, rackid)

Also I had slipped in the question on how to define that T/F column.

Cast your vote, one and all!

Many thanks - Jim

 

[r937]

Have I done something to offend you?

no, sorry, i did not intend to give that impression, i apologize (maybe i should've used a few more smilies, eh)


when the rest of the world understands primary and foreign keys as the mechanism for defining relationships between tables, it is counter-productive and misleading to claim that they don't

that's all

to get back to the original question, jim, i thought i already pointed out why it isn't a one-to-many relationship, it's a many-to-many relationship

r937.com | rudy.ca

 

[JGH]

Are you saying the way to do it is as a many-to-many, or that spices in a rack are a many-to-many? Spices was an arbitrary example that turned out surprisingly controversial.

 

[r937]

controversial? okay, i will take the blame for the offtopic discussion of what a relationship is or isn't

but as far as the difference between one-to-many and a many-to-many structures is concerned, i think it's a marvellously appropriate example

if we're not sure which of these (1-m or m-n) the spice/spicerack example is, please remember my comment about the oregano in rack 1 not being the same as the oregano in rack 2

we should not abandon this example, and the problem of the default spice question, if we're not sure which it is





r937.com | rudy.ca