Designing database for table which can has multiple optional column and dependencies

Question

Good Day to everyone in SO.

I have problem in designing database for my project, which is eyelet factory production module. Just for record, I use MySQL, C# .NET Framework, and NHibernate for my project.

Background Story

For my project, there will be Item table. Item defines, well, all item included in production of eyelet. It can be Raw Material (Coil, Paint, Plate, etc), Intermediate Item (Punch Item, for now) and Final Product (Assembly Item, Polished Item, Colored Item and so on) which can be sold.

All item has Name, Stock, Threshold column, but SOME item can have Outer Diameter or Color column for example, and some item dont have that. The Optional Column here is dependent to Item Type ( e.g Coil only has Outer Diameter, Inner Diameter, and Height. While Paint only has Color, and Final Product has all of them) . Item also has multiple Unit (Kg, Pcs, Pack, etc) which can be converted to other unit. Again, some item may only has Kg unit associated while other item can has Kg and Pcs unit associated (and conversion rate between them).

This is what I had in my mind.

1. Item
   • ID
   • ItemType_ID [FK]
   • Name
   • Stock
   • Treshold
   • Timestamp (CreatedAt, UpdatedAt, DeletedAt)
   • IsDeleted
2. Dimension
   • Item_ID [FK]
   • Outer Diameter
   • Inner Diameter
   • Height
3. Color
   • Item_ID [FK]
   • Color
4. ItemType
   • ID
   • Name
5. Unit
   • ID
   • Name

6. Item_Unit associative table

   • Item_ID [PK]
   • Unit_ID [PK]

   ... Not yet finished, came here for advice.

The things I want to ask are :

1. This is my second design, while my first design puts all optional column in Item table, and assign null when User didn't input the value for that column. Is it better to use my first design? Cause it simplify my code (Select only 1 table). But I heard that it is not good to have null value in database?
2. Is it better to use Inheritance in this scenario? Using parent table and child table ( Item table and Coil table as child) ?
3. For assembly item, colored item, polished item, they have prerequisite item which can be Intermediate Item or Other Final Item ( Some Colored Item can be made from Intermediate Item + Paint, and some other colored item can be made from assembly item + paint, while assembly item is always made of intermediate items). How do I design the table for easy lookup? In my main production form, there will be combobox to pick component.
4. How do I store item conversion rate in this scenario?

Any advice, correction or even a whole make over are appreciated. Thank You Very Much

Answer 1

With one table for every item you will be having a lot of null columns. This is not termed as bad design in certain cases where the Items doesn't vary much.

I would suggest if your Items are having too much different attributes than either use Parent-Child relation or the best way would be to use Entity–attribute–value model

This model will have fewer columns but more rows. Check if this model works for your problem.