We are migrating our legacy application in sitecore. We have a large database that we want to reuse in our sitecore application specially dbo.Product table. Product table contains ProductName, ProductID and other foreign key references. We are using buckets for Product creation in sitecore, I want to create all the Products from external database with existing fields(ProductID, ProductName etc) and apply corresponding renderings.There are many child items under each product. What is the best approach to solve this puzzle?
I would discourage you from copying your items into bucket in this case. According to Sitecore's documentation:
When you store items in an item bucket, the parent-to-child relationship between the items is removed, and instead the items are organized in a folder structure according to the date and time they were created. Therefore, the item buckets feature is primarily designed for storing content items that do not need to be stored in a hierarchy.
Your description of a problem states that you do have an important parent-to-child relationshipt. In such a case I would recommend one of the two possible solutions:
If you have small number of products (I assume 3k is number of all the items including FAQs, newsletters, etc.) you can easily go with importing them as regular items
If you plan to have larger database of products you could add only the products into a bucket and other items (FAQs, newsletters, etc.) as regular items under a separate item and connect them using for example multilist.
Does this approach solves your problem?
To import the items you could for example create a script using a bit of C# combined with the Sitecore Powershell module. Or you could do it purely in Powershell if you can extract the current data into a CSV. The module comes with a bunch of examples to get you started.
If you want to import the data into a products bucket but still retain the relationship between the product and the child items, then you need to remember to lock the relationship at the template level.