how magento stores product details in multiple websie store?

Question

how the product details are stored for each store and website?

is magento clones product details for every store, websites separately OR

it done by field values of the product tables.

because we can change almost all the details in each stores , so it is easy to clone a product and store the data related to each store/website as a separate entity

Answer 1

Magento uses for products (categories, customers and customer addresses) the EAV paradigm.
This means that instead of having a single table and one column corresponds to one property, you have 3 tables (actually more, but I will get to that later).

• one for the entity where you keep the id and things that don't depend on the environment (craation time for example).
• one table that contains the attributes (color, size).
• one table that contains the values for entities relative to an attribute. (entity id, color id, color value).

Magento has more that one of these last tables. It has one for each attribute type. (datetime, varchar, text, ...).
The main table is catalog_product_entity, the attribute table is eav_attribute and the tables that hold the values for entities and attributes are catalog_product_entity_* where * is int, decimal, datetime, varchar, text.

Using this approach, magento introduces the store id in the entity_* tables.
So for one entity and one attribute you can have more that one value.
The default values have the value for column store_id zero and the values for specific stores have the column store_id the actual id of the store.
When retrieving the values for frontend for a specific id, magento checks if there is a record for each attribute for that specific store id. If there is, it uses that, if no it checks for the value with store id zero.

But... even if the EAV paradigm is really extensible, it does not perform well. So for this magento introduced the flat tables. catalog_product_flat_* where * is the store id.
But these tables hold only a part of the product attribute values and you should not use them for writing.
Anything you write in there will be deleted the next time you rebuild the indexes.

I advice against writing directly in the database to clone products. It can easily lead to chaos.

You should use the internal API to clone products. See how the duplicate functionality works in the backend.