Database Modeling - Conditional Relationships
-
This is probably a really basic database design question, but for some reason, I'm struggling with it. The real world problem I'm trying to solve is a little too escoteric to describe, so I'll try to use a generic example that presents the same problem. Say a store sells two kinds of cogs. Each type of cog is represented in the database with its own table (they have sufficiently different characteristics to warent different table schemas). A customer can buy both kinds of cogs, and we want to keep track of their purchases. So we have a sales table that's associates the customer with the cogs he/she buys. One foreign key points to the customer, and another foreign points to the cog. What's got me stumped is that the foreign key that points to the cog could be associated with either type of cogs. How do we know which? Do we create an additional field that tells us the type of cog the customer purchased? That rings alarms for me in that it's surely unnormalized. So instead do we have seperate sales table for each type of cog? But wouldn't this lead to an explosion of tables as we add more cogs types to our inventory? This must seem like a beginner's question; I'm an old C++ audio programmer who's become a 'web developer', so occasionally I get stumped on DB basics. Any help is appreciated. :)
-
This is probably a really basic database design question, but for some reason, I'm struggling with it. The real world problem I'm trying to solve is a little too escoteric to describe, so I'll try to use a generic example that presents the same problem. Say a store sells two kinds of cogs. Each type of cog is represented in the database with its own table (they have sufficiently different characteristics to warent different table schemas). A customer can buy both kinds of cogs, and we want to keep track of their purchases. So we have a sales table that's associates the customer with the cogs he/she buys. One foreign key points to the customer, and another foreign points to the cog. What's got me stumped is that the foreign key that points to the cog could be associated with either type of cogs. How do we know which? Do we create an additional field that tells us the type of cog the customer purchased? That rings alarms for me in that it's surely unnormalized. So instead do we have seperate sales table for each type of cog? But wouldn't this lead to an explosion of tables as we add more cogs types to our inventory? This must seem like a beginner's question; I'm an old C++ audio programmer who's become a 'web developer', so occasionally I get stumped on DB basics. Any help is appreciated. :)
You need to find a way of describing n widgets from a single table so that the problem of new widgets does not require more tables which means a nightmare in maintenance and changing application pages that add, edit and display the data. Even if you can break it down to tables of classes of widgets you might be able to have a widget type table that keys into a widget class table. Even then you may find that you need to add new widget classes to cope but if you can make the table columns generic enough this may not be an issue. Just a quick thought.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
-
You need to find a way of describing n widgets from a single table so that the problem of new widgets does not require more tables which means a nightmare in maintenance and changing application pages that add, edit and display the data. Even if you can break it down to tables of classes of widgets you might be able to have a widget type table that keys into a widget class table. Even then you may find that you need to add new widget classes to cope but if you can make the table columns generic enough this may not be an issue. Just a quick thought.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum me, me, me
Thanks for your reply. The solution I've settled on uses part of your answer. I have a 'base' table for all items. Then I have tables where needed for 'derived' sub types. These derived tables have a 'zero to one' relationship to the base table. The derived tables are a way of extending the properties of items in the base table. So the items table could look like this:
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | wheel | $1.23 |
+----+--------+-------+
| 2 | wire | $1.50 |
+----+--------+-------+Then if there are more than one kind of cogs, I can subclass them in 'derived' tables (like derived classes in object oriented programming).
wheels table
+----+--------+--------+
| id | cog_id | radius |
+----+--------+--------+
| 1 | 1 | 50" |
+----+--------+--------+wires table
+----+--------+--------+
| id | cog_id | length |
+----+--------+--------+
| 1 | 2 | 12" |
+----+--------+--------+I can then do joins to get back only wheels, and another join to get just wires. I'm not sure if this is a common approach. I know the joins will cost more processing, but at least the schema makes sense to me.
-
Thanks for your reply. The solution I've settled on uses part of your answer. I have a 'base' table for all items. Then I have tables where needed for 'derived' sub types. These derived tables have a 'zero to one' relationship to the base table. The derived tables are a way of extending the properties of items in the base table. So the items table could look like this:
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | wheel | $1.23 |
+----+--------+-------+
| 2 | wire | $1.50 |
+----+--------+-------+Then if there are more than one kind of cogs, I can subclass them in 'derived' tables (like derived classes in object oriented programming).
wheels table
+----+--------+--------+
| id | cog_id | radius |
+----+--------+--------+
| 1 | 1 | 50" |
+----+--------+--------+wires table
+----+--------+--------+
| id | cog_id | length |
+----+--------+--------+
| 1 | 2 | 12" |
+----+--------+--------+I can then do joins to get back only wheels, and another join to get just wires. I'm not sure if this is a common approach. I know the joins will cost more processing, but at least the schema makes sense to me.
Leslie Sanford wrote:
I'm not sure if this is a common approach.
It is. And it's one that I strongly prefer over a solution where one "extends" the record by linking to a table with "custom fields" (most often key/value pairs of strings).
Leslie Sanford wrote:
I know the joins will cost more processing, but at least the schema makes sense to me.
..just a little extra overhead. Other alternatives would only make it worse, you'd end up with a lot of empty fields or with weird join-constructions.
Bastard Programmer from Hell :suss:
-
This is probably a really basic database design question, but for some reason, I'm struggling with it. The real world problem I'm trying to solve is a little too escoteric to describe, so I'll try to use a generic example that presents the same problem. Say a store sells two kinds of cogs. Each type of cog is represented in the database with its own table (they have sufficiently different characteristics to warent different table schemas). A customer can buy both kinds of cogs, and we want to keep track of their purchases. So we have a sales table that's associates the customer with the cogs he/she buys. One foreign key points to the customer, and another foreign points to the cog. What's got me stumped is that the foreign key that points to the cog could be associated with either type of cogs. How do we know which? Do we create an additional field that tells us the type of cog the customer purchased? That rings alarms for me in that it's surely unnormalized. So instead do we have seperate sales table for each type of cog? But wouldn't this lead to an explosion of tables as we add more cogs types to our inventory? This must seem like a beginner's question; I'm an old C++ audio programmer who's become a 'web developer', so occasionally I get stumped on DB basics. Any help is appreciated. :)