-1

My apologies if stackoverflow is not the best place for this question.

I'm relatively new to SQL still so I have a question about the best way to handle certain information. My job is to populate products on an ecommerce site with their relevant PDF files. This can range from product manuals, to CAD drawings, brochures, data sheets, so on and so on.

At first it seems like I'd want to give each category of downloadable file it's own column in the database. But that's going to get bloated as we sell a very large range of products, so the total number of downloadable file categories is going to be ridiculous.

My second thought was to load the data all into one column, but use something like JSON. When the data is pulled and read on the website I could read the JSON server-side to create the listing of filename titles and urls in a nice HTML list.

Is there a third option that I am overlooking? What's the best practice here?

jkupczak
  • 2,891
  • 8
  • 33
  • 55
  • 1
    so what's the problem with having `itemID, categoryID, file_data` in a single table? – Marc B Jun 02 '15 at 17:16
  • Nothing I guess. Being new to SQL I was wondering if adding lots and lots of fields would have unintended consequences or not. And if it is a concern then what's the best alternative. – jkupczak Jun 02 '15 at 17:25
  • Yes, having multiple columns has drawbacks - queries get bloated, adding a column is a code/database change, etc. Better to have a single table with a Category column (or a foreign key to a Category table) – D Stanley Jun 02 '15 at 17:27

1 Answers1

0

What actual information are you storing regarding the files, just the Filename and URL?

I would probably go for a single table, with a column for specifying the document type. Assuming you have a central table with a unique key for the ProductId, it might look like this

ProductId | DocType     | Filename | URL
=================================================
1         |CAD          |name1.pdf | http://...
1         |Manual       |name2.pdf | http://...
2         |Marketing Img|name3.jpg | http://...
...
Andrew
  • 1
  • 3
  • As far as downloadable files goes, yes, that is exactly what I need. Though I'm curious why you have both filename and URL. In my case I think I'd only need one, it would just be the location of the file on our site. My issue is that we already have a table where we list out a products specifications (weight, dimensions, etc) and in that table we also have three columns for downloadable files. But we're about to need to a whole lot more. So I was concerned about the size becoming to big. Or if too big is even a factor I should be concerned with. – jkupczak Jun 02 '15 at 17:31
  • Generally, I would say that best practice would be to pull the downloadable file details out into it's own table if you are going to have a 1 to many relationship between products and files - particularly if you have a large number of file types and the combination of documents per product is likely to differ between products. If you are concerned about table size, you may want to look at this post : http://stackoverflow.com/questions/1729515/sql-table-size-and-query-performance – Andrew Jun 02 '15 at 17:43