Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question

2 Answers

up vote 2 down vote accepted

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.

share|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
up vote 3 down vote

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'
share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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