Question about normalized tables (1 Viewer)

[MR_G]

I have been posting in the theory and database structure formum, but i thought this ? might be better received here:

If I have a table for say, Sales_Items and there are different types of sales items, say food and clothing, is it okay to have fields that some items don't use?

ex.

TABLE:
tbl_SALES_ITEMS
FIELDS:
SALES_ITEM_ID (PK)
SALES_ITEM_NAME
SALES_ITEM_SIZE
SALES_ITEM_COLOR

If I have a sales item: Hamburger, it will get a null for SIZE and COLOR. This doesn't seems right. But in previous posts, people were suggesting that I keep all of my sales items together and not separate them into individual tables.

The problem with separating them is that, when I go to put them all together in a form were users pick a sales item for a receipt, it doesn't work very well. Sorry, I am babbling now.

The question is: Is it okay to have null values in a table like tbl_SALES as described above, or am I missing something?

Thanks in advance!

MR_G

 

[boblarson]

The simple answer is, yes it is okay to not use fields for everything. However, if you start to get too many of those "possible" fields then I would suggest that you might think about moving to another table to handle the items that might be applicable to only some things.

 

[Len Boorman]

When you analyse an entity then you establish the attributes of that item. Now it is quite in order to have null values for an attribute but it would, I suggest be good practice to attempt toestablish anentry for all attributes.

Why ?.
A null value ?. Is it Null or is it missing. That is the quaetion as they say.

So firstly how about a N/A value for Colour when associated with hamburger. ?

That determines that Colour is not null and therefore there is not a missing entry.

You can also find all items where Colour is not applicable. Regarding Size.

How about another attribute called Unit of measure. therefore Hamburger has a Size say 4 and a unit of measure of Ounces say. This Unit of measure becomes applicable to all items that have a value for size. I suggest you have a control table for UOM's. That way you will also control what UOM's can be used and allows a search using both Size and UOM to find items.

e.g. Search Size 4 and UOM Ounce and you get the hamburger, sausage or whatever

Len

 

[MR_G]

Thanks guys,

I wish I would have found this forum long ago. Truly appreciate the help.

MR_G

 

[Len Boorman]

Mr G
help is not a problem for people on this forum.

It is good to get a response back, that helps the helpers.

If you click on the scales on the helpers post you can add Reputation points.

Just a bit of info for next time

Len B