How should I (if I should at all) implement Generic DB Tables without falling into the Inner-platform effect?

Question

I have a db model like this:

tb_Computer (N - N) tb_Computer_Peripheral (N - 1) tb_Peripheral

Each computer has N peripherals. But each peripheral is different in nature, and will have different fields. A keyboard will have model, language, etc, and a network card has specification about speed and such.

But I don't think it's viable to create as many tables as there are peripherals. Because one day someone will come up with a very specific peripheral and I don't want him to be unable to add it just because it is not a keyboard neither a network card.

Is it a bad practice to create a field data inside tb_Peripheral which contains JSON data about a specific peripheral?

I could even create a tb_PeripheralType with specific information about which data a specific type of peripheral has.

I read about this in many places and found everywhere that this is a bad practice, but I can't think of any other way to implement this the way I want, completely dynamic.

What is the best way to achieve what I want? Is the current model wrong? What would you do ?

Answer 1

Only one question to answer when you do this sort of design. JSON, a serialised object, xml, or heaven forbid a csv, doesn't really matter.

Do you want to consume them outside of the API that knows the structure?

If you want to say use sql to get all peripherals of type keyboard with a number of keys property >= 102 say.

If you do, it gets messy, much messier than extra tables. No different to say having a table of pdfs or docs and trying to find all the ones which have more than 10 pages.

Gets even funnier if you want to version the content as your application evolves.

Have a look at a Nosql back end, it's designed for stuff like this, a relational database is not.

Answer 2

I can think of four options.

The first is to create a table peripherals that would have all the information you could want about peripherals. This would have NULLs in the columns where the field is not appropriate to the type. When a new peripheral is added, you would have to add the descriptive columns.

The second is to create a separate table for each peripheral.

The third is to encode the information in something like JSON.

The fourth is to store the data as pairs. So each peripheral would have many different rows.

There are also hybrids for these approaches. For instance, you could store common fields in a single table (ala (1)) and then have key value pairs for other values.

The question is how this information is going to be used. I do most of my work directly in SQL, so the worst option for me is (3). I don't want to parse strange information formats to get something potentially useful to a SQL query.

Option (4) is the most flexible, but it also requires more work to get a complete picture of all the possible attributes.

If I were starting from scratch, and I had a pretty good idea of what fields I wanted, then I would start with (1), a single table for peripherals. If I had requirements where peripherals and attributes would be changing fairly regularly, then I would seriously consider (4). If the tables are only being used by applications, then I might consider (3), but I would probably reject it anyway.

Answer 3

It's not a question of "good practices" or "bad practices". Making things completely dynamic has an upside and a downside. You have outlined the upside fairly well.

The downside of a completely dynamic design is that the process of turning the data into useful information is not nearly as routine as it is with a database that pins down the semantics of the data within the scope of the design.

Can you build a report and a report generating process that will adapt itself to the new structure of the data when you begin to add data about a new kind of peripheral? If you end up stuck with doing maintenance on the application when requirements change, what have you gained by making the database design completely dynamic?

PS: If the changes to the database design consist only of adding new tables, the "ripple effect" on your existing applications will be negligible.