Hope you can help me with the following problem...
I want to create a database to hold information on the parts bought-in by a company.
The problem I am faced with is that within these parts there are a number of different groups of similar parts - all with different attributes requiring storage for querying purposes. For example at a sports store the groups might be: balls, bats, shoes etc.
One possibility would be to have a single table, which fields for every possible attribute, and only fill in those of relevance, but I am against this due to the redundancy created.
Another possibility is to store the part numbers and common attributes in a table, along with a field specifying to which group it belongs. Then create a table for each group to store the attributes specific to that group. But again this is not desirable, since it lacks data integrity.
Another option could be to store complete tables for each group, but this could result in duplication of part numbers across the tables which is undesirablegat what I'm driving ar, and that someone can advise a better solution.
When you say attributes, what exactly do you mean?
If you can use a simple text description as an attribute, then you can do the whole thing in one table. However, if you have something like machining tools, which have an array of very unique attributes, then you are best served with having a table for each part category.
The attributes in question are a variety of things, material colour, delivery lead-times, dimensions, descriptions etc. basically alot of info. which are common arcoss all components, but then there are a number of things whihc aren't common, such as certifications, imoact ratings etc. which are all important characteristics for queryimng the parts.
My key concern is building a robust database without redundancy, which seems difficult, since I need some of the data for the MRP application I'm building, so ideally I want the relevant data (lead-time) in a single table, with the part number. But then the question arises of how to deal with the lose ends.