So, to get better at designing databases, I'm attempting to model a database system after the AR15 Builder.

Here is what I have so far: a busy cat

While it makes sense logically, I'm afraid there may be too much inheritance... Might there be a better way to handle this? Any tips appreciated; thanks in advance!


You might like to look here - they give a lot of sample data models.

There are two in particular which might be of use to you. Here is a BOM (Bill Of Materials) which looks as if it might be a good fit for your business (manufacturing machine guns). Take a look at the "Manufacturing" section (no. 13).

You appear to have a table for all your different components Barrel, Buttstock, Sight and Scope amongst others. I'm not so sure that they're all necessary? But, as Einstein said, things should be made as simple as required, but no simpler.

What's your RDBMS? It's always good on this site when asking a question to provide your database server.

Take the tour of the site, visit the help centre and check out the "Help us to help you" blog. p.s. welcome to the dba.stackexchange.

  • Thank you for the resources! Seems they'll help guide me in the right direction. – Jester Jeffrey Feb 22 '16 at 5:22

When you say "too much inheritance" that could mean a whole lot of things.

First off, you need to analyze the information requirements before you design the database. A good step in analysis is to analyze the subject matter from a data centric point of view. A good model for this purpose is the ER model. ER models have inheritance, only the ER people call it "generalization/specialization". When it comes to requirements analysis, you don't have "too much" inheritance if you have the amount you need to express the subject matter correctly.

But your question is about design, not analysis. From your diagram, it looks like you are designing a relational database, and accordingly coming up with a relational model for it. Your use of foreign keys to express relationships indicates that.

This is where things get messy. The relational model, as such, does not have inheritance built into it. This is different from object models. You need to design tables (or, if you prefer, relations) that adequately reflect the inheritance relationships inherent in your subject matter, and will be useful to you in managing data and using data from an application. Your design, at this point, doesn't seem to say how the tables (relations) are going to be organized.

There are three design techniques you can look up that help solve the problem of expressing inheritance in relational tables. Here they are: single table inheritance; class table inheritance; shared primary key.

The first two are alternatives. They each have an upside and a downside. The third one is a handy add on to class table inheritance.

You have to decide, for your case, how much of this is "too much" based on case details that you haven't presented here.

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.