how to keep category for a product in db

Question

I have some questions related about how to keep the category information for a product in db. For example the data source for my app is an xml which contains informations for some products:

<product>
 <productId>728737283</productId>
 <productName>chocolate</product>
 <productPrice>78</productPrice>
 <productCategory>Sweets</productCategory>
</product>
<!------other products--------->

My app must parse the info related to each product and save in the db, after all products have been parsed then the info is saved into db for each product. There are some constraints: the xml will be parsed using StAX parser, and there is allowed to traverse the xml once. The matter is that in the db I will have 2 tables : one for products and one for related categories of the products. The category table will have 2 columns :

CategoryId(PK, AI),CategoryName(UNIQUE)

And in Product table there will exists a column related to categoryof the product. My question would be in this case: the column related to category from product table will contain the explicit name of the category or the category id? Would be normally that the CategoryId from category table to be foreign key in Product table? But in this case I will have an issue when inserting a new product in db, I won't know the categoryId, because xml contains only the category name. How is better to proceed in this case? I am interested also about how should look the right DAOs used in this case, the right DTOs and the right data models.

Answer 1

In your product table you should refer to the category ID and not category name. Foreign keys should always be the referenced table's ID.

So if you have a product node with the category name, you have first to check if there is already an entry in the category table. If yes, then get the category ID from there. If not you insert a new entry for that category name. To get access to the newly generated category ID see this answer: PreparedStatement with Statement.RETURN_GENERATED_KEYS

Then you can insert the new record into the product table.

To avoid a select form category table for each new product you can hold a map storing the category names as keys and the generated category IDs as values. But this only works when such a map would not exceed the available memory and when the category table is empty before reading the XML.