5

I have a product table called products It has 3 fields (name,model(PK), and class_name). The class corresponds to a table. So here is an example:

Product table:

model | name | class_Name
z123  | Abcd | AMPS

AMPS table:

model | attribute_1 | attribute_2
z123  | blah blah   | blah blah

Question:

Should I have a table that holds the PK (model) and its corresponding class name, and then use the class ID in my product table? Would it be more efficient to have a table that holds all of the model's and their classes?

Çağdaş Tekin
  • 16,592
  • 4
  • 49
  • 58
Ryan
  • 14,392
  • 8
  • 62
  • 102
  • 2
    Added the SQL tag so you can actually get answers. – Dhaivat Pandya Feb 22 '12 at 23:50
  • I don't see anything wrong with what you're doing, and I doubt that adding tables will help. However, you should consider asking a more specific question. What is your goal? If you're worried about efficiency, what are you trying to optimize? – Paul Keister Feb 23 '12 at 00:10
  • @Paul Keister. I'm just trying to do things the "right way". – Ryan Feb 23 '12 at 00:13

2 Answers2

12

There's already an accepted answer, but I'm adding the following for the benefit of other people who run across this Q. Note that this solution is markedly different from indicating the subclass in the master table. And in my opinion it's both simpler and more flexible.

Your case looks like an instance of the design pattern known as “Generalization Specialization” (Gen-Spec for short). The gen-spec pattern is familiar to object oriented programmers. It’s covered in tutorials when teaching about inheritance and subclasses.

The design of SQL tables that implement the gen-spec pattern can be a little tricky. Database design tutorials often gloss over this topic. But it comes up again and again in practice.

If you search the web on “generalization specialization relational modeling” you’ll find several useful articles that teach you how to do this. You’ll also be pointed to several times this topic has come up before in this forum.

The articles generally show you how to design a single table to capture all the generalized data and one specialized table for each subclass that will contain all the data specific to that subclass. The interesting part involves the primary key for the subclass tables. You won’t use the autonumber feature of the DBMS to populate the sub class primary key. Instead, you’ll program the application to propagate the primary key value obtained for the generalized table to the appropriate subclass table.

This creates a two way association between the generalized data and the specialized data. A simple view for each specialized subclass will collect generalized and specialized data together. It’s easy once you get the hang of it, and it performs fairly well.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
6

This looks like a "subclass" (aka. "category") hierarchy. If you have and always will have only AMPS and no other "child class", then you may consider merging it with Product. Otherwise, it looks good.

BTW, there are 3 ways to implement class hierarchies in relational databases, each with strengths and weaknesses. Keeping all classes in a single table is one of them, but can be hard to maintain and can be problematic for certain kinds of referential integrity. The model that you are already using ("class per table") should probably be your default choice unless there is a compelling reason otherwise...

Community
  • 1
  • 1
Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • Yes there will always be a specific class per product. I am aware of the 3 ways to implement the class hierarchies, and "class per table" looked like the most logical way to solve the particular problem I had. Thanks – Ryan Feb 23 '12 at 02:24