I'm a newbie in database design, hope you guys can help me with the following scenario.
Let's say, I have 5 Item Types with some common fields and unique fields among them. Example:
Item Type 1 : A, B, C, D, E, F, G
Item Type 2 : A, B, C, H, I, J, K
Item Type 3 : A, B, C, E, J, K, L, M
Item Type 4 : A, B, C, F, H, K, N, O
Item Type 5 : A, B, C, G, H, E, O, P, Q
Now, my current table design is, I have 1 master table called Item with columns A, B, C and then have 5 different Item SubType tables to store the rest of the unique columns of the respective Item Type
However, the above design would requires me to join table, hence it would affect the query performance.
So, I were to create just 1 table to store all Item Types with columns A, B, C, D, E, F, G, H, I, J, K , L, M, N, O, P, Q would that be a good idea?
Yes, some of the columns will be NULL for different type of items, but in term of query performance, it would be easier and faster to retrieve the data. Would it?
Note: In the actual scenario, there more ItemType (not just 5) and many more columns.
Thank you in advance!