0

I have a task to associate 1:N documents to a record in my hardware table. This file could be a warranty, or whatever. In SQLServer I set up a FileStream group and made a table for only the file and the other required columns (id, internal id (a guid), and a varbinary(MAX) column). I have another table that will store the file information (name, size, mime type), and the file's id in from the files table. I call this table Document_Info. Is this a good name? Describing the relationship between my hardware table and the Document_Info table I'm struggling with.

I'm considering storing the hardware's id in the Document_Info table and create another column representing what this document is related to. That would help me integrate other future tables that also need to relate 1:1 or 1:N if they need to store files as well. I just specify a new type and I'm good. id's can be the same as long as their id type is different.

How this all relates to my hardware model is, by principle, it doesn't because a model's fields represents a table's columns. Yet, I have to store this hardware's documents somewhere, but the question is where?

Improve this question
1
  • Coming back a week later I have a feeling this question goes no where and is almost asking for opinions.
    – Luminous
    May 16, 2015 at 5:48

1 Answer 1

Reset to default
1

Is this your basic model, sorry, not sure if I understand your text:

Hardware_table(Id, GUID, Description, etc) this holds your primary object one of each Document_info_table(Id, HId, Name, size, mime type) this holds documents related to primary object

Using SQL you would have to link Hardware_Table to Document_Info_table by inserting the PK from the first into each related record into the latter table. Names of tables don't really matter since these are shielded from end users by your programming interface.

I would not store the actual documents into the SQL tables unless you really have a need do do that, I dont like reading and writing blob data into SQL. BEtter to store in the file system and store the pointers in the SQL databas, better use of DB storage and read/write efficiency.

Improve this answer
3
  • I would go HardwareID as opposed by HId just in case there is another table that starts with an 'H'.
    – Jon Raynor
    May 15, 2015 at 20:57
  • The files are stored in the filesystem. They aren't blobs. Where do you see blobs at anyway? It's a FileStream.
    – Luminous
    May 16, 2015 at 5:43
  • Also what you're explaining is exactly what I'm avoiding.
    – Luminous
    May 16, 2015 at 5:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.