Database Design

Question

This is a general database question, not related to any particular database or programming language.

I've done some database work before, but it's generally just been whatever works. This time I want to plan for the future.

I have one table that stores a list of spare parts. Name, Part Number, Location etc. I also need to store which device(s) they are applicable too.

One way to do is to create a column for each device in my spare parts table. This is how it's being done in the current database. One concern is if in the future I want to add a new device I have to create a new column, but it makes the programming easier.

My idea is to create a separate Applicability table. It would store the Part ID and Device ID, if a part is applicable to more than one device it would have more than one row.

Parts
-------
ID
Name
Description
Etc...

PartsApplicability
-------
ID
PartID
DeviceID

Devices
------
ID
Name

My questions are whether this is a valid way to do it, would it provide an advantage over the original way, and is there any better ways to do it?

Thanks for any answers.

Answer 1

You're describing the standard setup of a many-to-many relationship in an RDBMS, using an intermediate join table. Definitely the way to go if that's how your model will end up working.

Answer 2

I agree with Rex M's answer, this is a standard approach. One thing you could do on the PartsApplicability table is remove the ID column, and make the PartID/DeviceID a composite primary key. This will ensure that your Part cannot be associated to the same Device more than once, and vice-versa.

Answer 3

Using a separate table to hold many-to-many relationships is the right way to go.

Some of the benefits for join tables are

1. Parts may be applicable to any device and creating new devices or parts will not lead to modifications to the database schema
2. You don't have to save nulls or other sentinental values for each part-device mapping that doesn't exists i.e. things will be cleaner
3. Your tables remain narrow which makes them easier to understand

You seem to be on your way to discover the database normal forms. The 3rd normal form or BNF should be a good goal to have although sometimes it's a good idea to break the rules.

Answer 4

Your second design is a very good design, and similar to what I've done (at work and on my own projects) many times in terms of describing relationships between things. Lookup tables and their equivalent are often far simpler to use than trying to stuff everything in one table.

Would also agree on making the programming easier. Ultimately, you'll find that learning more makes programming far easier than trying to push things into what you already know even when they really don't fit. Knowing how to properly join tables and the like will make your programming with databases far easier than continually modifying columns would be.