Let's say I have table of cars and table of car manufacturers. Each car references one manufacturer.

Furthermore, each car that has manufacturer x requires an extra field. Say, cars from that manufacturer have an extra feature that needs configuring that no other manufacturer provides.

Where does this field go?

I think there should be another table with a one-one mapping between it and the cars table which holds the extra field for cars of that manufacturer.

So given this, how do I make sure that only cars of the correct type have this extra data in the other table? Or is this really bad data design?


What you are proposing is an entity subtyping approach. This would be one common solution to your design problem. Another would be Entity-Attribute-Value (EAV).

Type "subtype" or "EAV" into the search box in the top right hand corner of the page and you'll see many questions describing and discussing the relative pitfalls and merits of each.

Whether subtyping (or, alternatively EAV) is a "really bad design" in your case depends on exactly how many different unique features and manufacturers you need to account for and perhaps where you stand philosophically (some people insist that EAV is always evil-for example).

To answer your specific question about enforcing a match between brands and their unique attributes, the only way to do this is with procedural code. Depending on your DBMS you might be able to do this with triggers. There isn't a way to to it declaratively.

| improve this answer | |
  • Why do you say that that "enforcing a match between brands and their unique attributes" cannot be done declaratively? With a normalized design, this should be possible. Why not? – ypercubeᵀᴹ Mar 9 '12 at 15:18
  • @ypercube - How do you create a check constraint on a child table that imposes a particular FK value in the parent table? This can be done simply enough in a trigger (if one's DBMS supports triggers) but not using a non-procedural constraint. – Joel Brown Mar 9 '12 at 17:19
  • I'm not sure I follow. With "child" you mean the car in the OP's question? And with "parent" the manufacturer? An example (or pseudocode) would help me understand. – ypercubeᵀᴹ Mar 9 '12 at 22:25
  • @ypercube - The child is the subtype table containing a subset of attributes that only apply to cars (parent) of a particular manufacturer (grand-parent). OP mused about using subtyping but wants to impose a constraint that ensures the subtype is populated only for cars of the right manufacturer. The constraint is on the subtype but it relies on data that is in the car (parent). Unless you want to start defining (and redefining) your check constraint with every new record in car so that you can hard code the car FK value, no declarative constraint will do what OP is asking about. – Joel Brown Mar 10 '12 at 3:45

The features data should really exist in a separate table. Normalize your data for maintainability and scalability. For instance, you could implement something like this:

create table Manf
    pkManf int identity(1, 1) not null primary key clustered,
    Name varchar(256) not null

create table Car
    pkCar int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null

create table Features
    pkFeatures int identity(1, 1) not null primary key clustered,
    fkManf int not null foreign key references Manf(pkManf),
    Name varchar(256) not null

Just because you currently only have one manufacturer with a feature that needs to be noted in data, that doesn't mean that's always going to be the case. It is a very easy SELECT to retrieve all of the cars with a particular feature.

EDIT: To get all Cars with a particular feature, you'd do something like this:

select c.*
from Car c
inner join Manf m
on c.fkManf = m.pkManf
inner join Features f
on f.fkManf = m.pkManf
where f.Name = 'some feature'
| improve this answer | |
  • Wouldn't this also need a join table between Car and Features in order to set the values of the features for a car? – Ian Warburton Mar 9 '12 at 14:41
  • @IanWarburton As per your question, my understanding was that features were related to Manufacturers, not the cars themselves. To get all Cars that have this feature, see my edit. – Thomas Stringer Mar 9 '12 at 14:44
  • I reckon I've come up with a bad example. I should have stuck to my actual situation at hand and described it more technically. ;) Quick translation... the cars table holds instances of cars. Manufacturers should really be types of car (e.g. Model-T). Each car instance will need a value for each car feature e.g. color. So some car types have more features than others. I think Joel Brown has identified the issue here. – Ian Warburton Mar 9 '12 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.