Database Design Conundrum

Through a training program at my job, my programming skills have improved and I am pretty confident in my programming abilities. At home, I not only want to practice programming, but designing a software system properly as well. I eventually see myself in a position of technical leadership and being able to not only code, but also design is important.

To help me practice this, I came up with a scenario for an application that isn't so cut and dry (as business problems often are not) and now I am stuck on the database design.

The business problem or application purpose I came up with is an application (currently working a web version) that users can use to track their shopping at various stores. At the moment, it will be designed without interfacing with stores websites, but that is an advanced feature I hope to eventually figure out how to implement.

From a database perspective, I have multiple stores that sell multiple items, but there is some overlap with the items so I decided to put the full item information in a separate table so I don't repeat it. If you think about real-life businesses, there is a lot of overlap if you are taking about major department stores. Often times, I will see a lot of the same items they sell in Target in Meijer(a store out here in Ohio). A lot of the food you'll find in Meijer's grocery section is also found in Kroger so I feel like it would be redundant to repeat this information.

Then I'll have another table that lists store_id, item_id, and price so we know the price of items in a store and what items that store sells.

The tricky thing here is that for different categories of items, I need to store different type of information. Let's say I have clothing items and food item. Both types of items can have an id, name, and description, but for clothing, I will have a brand, color, and material. It doesn't make sense to store material for food necessarily, an ingredient list may be relevant, but that would be multiple fields.

So how do I organize this? I don't want null fields in my database. If I have different fields for different categories of items, then I could have a table that indicates item and category and then different tables for different categories, but then how would I access that via a join in SQL. After all, the table you are joining to is conditional on the category. Maybe this could be handled in a stored procedure somehow with if-else-else-if conditions. OR I could even join all the tables and then just use the condition where category=[the category I want]. The question is what is a good way to handle it.

Thoughts or suggestions? Of course, in the Business Object portion of things, these multiple joins mean not only POJOs (Plain Old Java Objects - it's just a class that really only represents an object's attributes), but classes that deal with result sets from multiple objects. I will have to figure out how to write those.

Software design is tricky business, but this is my passion and I want to be amazing at it.

Comments