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?
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.
3