How to create custom table in SDL tridion 2011?

Question

I am not sure whether to ask this question here. I have a requirement where I have to validate a product code entered by a user on a page. Client will provide product data in CSV format and that will be in thousands.

My question is: what is the best way to achieve this?

I thought to dump a csv file in some folder and then write .net code to read it. However this solution is supposed to be global and will be used by more than one site.

I thought to create WEB API/WCF Service which will read the Product data and verify against user product code. However I am not sure where should I store product data?

I also came across oData but not sure whether to use it for this solution.

My main concern is, where should the Product data be stored?

Answer 1

If you do not need to manage the product data as content in anyway (edit it, translate it, link to it, put it through workflow etc.) there is no reason to put it in a Content Management System like Tridion at all - particularly if it runs to thousands of items. Tridion just becomes a middleman sitting between your web application and Product Information Management system that you do not really need.

It sounds like your requirement (validate a product code entered by a visitor on your website) is totally unrelated to Content Management, and more in the field of Product Information Management.

Answer 2

From the sounds of it, you need to import thousands of products from Excel/CSV along with the product codes used to look them up in the system. This can be achieved fully outside of Tridion with a custom database (or XML file), or you can create a Custom Page (Core Service job) in the Tridion CM to facilitate importing your data into Components.

I've done similar implementations where the editor imported their CSV using a Custom Page. This Custom Page called the Core Service to create a "Product" component that had the product code set as a Custom Meta field. Then a Dynamic Component Template was used to publish the Components as DCPs and the website then queried the Broker by CustomMeta Criteria with the Product Code. Hope this helps.

Answer 3

As per your comments, below are the possibilities:

1. Store all the Product data in the form of tridion components
   • Use the components to publish as "Product XML"
   • Use the Product XML for validating the product page based on the user inputs
   • Here you can cache the product XML and invalidate the XML whenever it gets modified
2. Store all the Product data in the custom database
   • Query the database to validate the product page based on the user inputs

Update:

How to create a Page?

1. Create a Schema called "Product Schema" which contains one "MultiLine Text" Field
2. Create a Component with Product Schema
3. Dump all the CSV product data in "MultiLine Text" Field
4. Create a Component Template called "Product Template"
5. Component Template reads the field and render the product data as mentioned in the sample format
6. Create a page called "Product XML" with Product Component and Product Template
7. Publish the Product XML

Sample XML:

  <productData>
    <code>ABC</code>
    <code>DEF</code>
    ...
    ...
    <code>XYZ</code>
  </productData>