title: Restructure Inventory Management Database (2 to 3 Tables; Development Stage)
body: <p><strong>I’m developing a database.  I’d appreciate some help restructuring 2 to 3 tables</strong> so the database is both compliant with the first 3 normal forms; and practical to use and to expand on / add to in the future.  I want to invest time now to reduce effort / and confusion later.</p>
  
  <p><strong>PREAMBLE</strong></p>
  
  <p><em>Please be aware that I'm both a nube, and an amateur, though I have a certain amount of experience and skill and an abundance of enthusiasm!</em></p>
  
  <p><strong>BACKGROUND TO PROJECT</strong></p>
  
  <p>I am writing a small (though ambitious!) web application (using PHP and AJAX to a <strong>MySQL</strong> database).  It is essentially an <strong>inventory management</strong> system, for recording and viewing the current location of each individual piece of equipment, and its maintenance history.  If relevant, transactions will be very low (probably less than 100 a day, but with a possibility of simultaneous connections / operations).  Row count will also be very low (maybe a few thousand).</p>
  
  <p>It will deal with many completely different categories of equipment, eg bikes and lamps (to take random examples).  Each unit of equipment will have its details or specifications recorded in the database.  For a bike, an important specification might be frame colour, whereas a lamp it might require information regarding lampshade material.</p>
  
  <p>Since the <strong>categories of equipment have</strong> so <strong>little in common</strong>, I think the most logical way to store the information is <strong>1 table per category</strong>.  That way, each category can have columns specific to that category.</p>
  
  <p>I intend to store a <strong>list of categories in a separate table</strong>.  Each category will have an id which is unique to that category.  (Depending on the final design, this may function as a lookup table and / or as a table to run queries against.) There are likely to be very few categories (perhaps 10 to 20), unless the system is particulary succesful and it expands.</p>
  
  <p>A list of bikes will be held in the bikes table.
  Each bike will have an id which is unique to that bike (eg bike 0001).
  But the same id will exist in the lamp table (ie lamp 0001).</p>
  
  <p>With my application, I want the user to select (from a dropdown list) the category type (eg bike).
  They will then enter the object's numeric id (eg 0001).
  The combination of these two ids is sufficient information to uniquely identify an object.</p>
  
  <p><strong>Images:</strong></p>
  
  <p><strong>Current Table Design</strong>
  <img src="https://i.stack.imgur.com/HvgmY.png" alt="Current Table Design"></p>
  
  <p><strong>Proposed Additional Table</strong>
  <img src="https://i.stack.imgur.com/6w8Yu.png" alt="Proposed Additional Table"></p>
  
  <p><strong>PROBLEM</strong></p>
  
  <p>My gut feeling is that there should be an “<strong>overarching table</strong>” that encompasses every single article of equipment no matter what category it comes from.  This would be far simpler to query against than god knows how many mini tables.  But when I try to construct it, it seems like it will break various normal forms.  Eg introducing redundancy, possibility of inconsistency, referential integrity problems etc.  It also begins to look like a domain table.</p>
  
  <p>Perhaps the overarching table should be a <strong>query or</strong> view rather than an <strong>entity</strong>?</p>
  
  <p>Could you please have a look at the <strong>screenshots</strong> and let me know your opinion.  Thanks.</p>
  
  <p>For various reasons, I’d prefer to use surrogate keys rather than natural keys if possible.  Ideally, I’d prefer to have that surrogate key in a single column.</p>
  
  <p>Currently, the bike (or lamp) table uses just the first column as its primary key.  Should I expand this to a composite key including the <code>Equipment_Category_ID</code> column too?  Then make the <code>Equipment_Article</code> table into a view joining on these two columns (iteratively for each equipment category).  Optionally <code>Bike_ID</code> and <code>Lamp_ID</code> columns could be renamed to something generic like <code>Equipment_Article_ID</code>.  This might make the query simpler, but is there a risk of losing specificity?  It would / could still be qualified by the table name.</p>
  
  <p>Speaking of redundancy, the <code>Equipment_Category_ID</code> in the <em>current</em> lamp or bike tables seems a bit redundant (if every item / row in that table has the same value in that column).</p>
  
  <p><em>It all still sounds messy!  But surely this must be very common problem for eg online electronics stores, rental shops, etc. Hopefully someone will say oh that old chestnut!  Fingers crossed!  Sorry for not being concise, but I couldn't work out what bits to leave out.  Most of it seems relevant, if a bit chatty.  Thanks in advance.</em></p>
  
  <hr>
  
  <p><strong>UPDATE 27/03/2014</strong>  <em>(Reply to @ElliotSchmelliot)</em></p>
  
  <p>Hi Elliot.<br>
  Thanks for you reply and for pointing me in the right direction.  I studied OOP (in Java) but wasn't aware that something similar was possible in SQL.  I read the link you sent with interest, and the rest of the site/book looks like a great resource.</p>
  
  <p><strong>Does MySQL InnoDB Support Specialization &amp; Generalization?</strong><br>
  Unfortunately, after 3 hours searching and reading, I still can't find the answer to this question.  Keywords I'm searching with include: MySQL + (inheritance | EER | specialization | generalization | parent | child | class | subclass).  The only positive result I found is here: <a href="http://en.wikipedia.org/wiki/Enhanced_entity%E2%80%93relationship_model" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/Enhanced_entity%E2%80%93relationship_model</a>.  It mentions MySQL Workbench.</p>
  
  <p><strong>Possible Redundancy of Equipment_Category</strong> (Table 3)<br>
  Yes and No.  Because this is a lookup table, it currently has a function.  <em>However</em> because every item in the <code>Lamp</code> or the <code>Bike</code> table is of the same category, the column itself may be redundant; and if it is then the <code>Equipment_Category</code> table may be redundant... unless it is required elsewhere.  I <em>had</em> intended to use it as the RowSource / OptionList for a webform dropdown.  Would it not also be handy to have Equipment_Category as a column in the proposed <code>Equipment</code> parent table.  Without it, how would one return a list of all <code>Equipment_Name</code>s for the Lamp category (ignoring distinct for the moment).</p>
  
  <p><strong>Implementation</strong><br>
  I have no way of knowing what new categories of equipment may need to be added in future, so I’ll have to limit attributes included in the superclass / parent to those I am 100% sure would be common to all (or allow nulls I suppose); sacrificing duplication in many child tables for increased flexibility and hopefully simpler maintenance in the long run.  This is particulary important as we will not have professional IT support for this project.</p>
  
  <p>Changes really do have to be automated.  So I like the idea of the <strong>stored procedure</strong>.  And the <code>CreateBike</code> example sounds familiar (in principle if not in syntax) to creating an instance of a class in Java.</p>
  
  <p><em>Lots to think about and to teach myself!  If you have any other comments, suggestions etc, they'd be most welcome.  And, could you let me know what software you used to create your UML diagram.  Its styling is much better than those that I've used.</em></p>
  
  <p>Cheers!</p>