CODEWITHSUNDEEP
databasedatabase-designdata-structures
nubm
nubm

Reputation: 1153

DB tables design with a lot of attributes

I'd like to know how the db design should look like in this case.

enter image description here

What comes to my mind is a table for every entity like: 

Engine  
EngineDesign  
ElectricalSystem  
Drivetrain  
etc.

But it looks to me as a lot of tables. Is my idea ok or is it excessive?

And what about the Drivetrain? Today we have 8 gears but it can change whenever a manufacturer comes with something different.

Should it be like this:
Table (columns) 

DrivetrainId | 1st | 2nd | 3rd | etc.

Or

Table (rows) 

DrivetrainId | Gears
1            | 1st
2            | 2nd
3            | 3rd
etc.

Upvotes: 0

Views: 176

Answers (2)

saritonin
saritonin

Reputation: 247

My first instinct would be to do something with an Attributes table and a Cars table (I'm assuming cars?)

Attributes
AttributeId | AttributeCategory | AttributeDesc

Cars    
CarId | AttributeId | AttributeValue

Edit: See comments below @HLGEM's explanation why an Entity-Attribute-Value table is not a good idea in this example.

Upvotes: -1

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

You can't judge the soundness of a database design by counting tables. There's no such thing as "too many tables" normal form, or "not enough tables" normal form. There's also no such thing as "too many columns" normal form, or "not enough columns" normal form.

As it stands today, having one column for each of the transmission gear ratios might still get you to 5NF. That's because it's not having multiple columns whose values come from the same domain that's a problem. It's having multiple columns whose values come from the same domain and have the same meaning that's a problem.

Clearly, 8th gear and 1st gear have different meanings. In fact, you might consider them to be drawn from different domains. My guess is that 0.67 isn't a valid value for 1st gear, and that 4.85 isn't a valid value for 8th.

When these are stored in different columns,

  • it's easy to enforce the constraint that every row have a value for each of the 8 transmission gear ratios, and
  • constraints on the range of valid values for each gear ratio is really simple. (But you have to accommodate NULLs for later designs having only 5 or 6 gears, and that raises normalization issues.)

When they're stored as rows,

  • it's harder--perhaps impossible--to enforce the constraint that each vehicle have a value for each of the 8 transmission gear ratios, and
  • constraints on the range of valid values for each gear ratio is more complicated. (Especially when you later accommodate designs that have only 5 or 6 gears.)

Upvotes: 2

Related Questions