0

I have tables like Customer, Purchase etc which sometimes have associated documents with them, by documents I mean some file somewhere (like a scanned drivers license or something)

We cannot have the application upload these documents straight into the database so instead I have a uniqueidentifier column for these (Should I have a file hash instead?)

My question:
In the future we might have more documents associated with a table, so I was thinking of adding extra fields like:

Customer
+DriversLicenseDoc
+Document1//for the future
+Document2 //future use

So in the future if they do decide they want another document I will just have to update my entity-framework model and rename the column in my model and the database won't have to change?

Is this how its generally done? Any better ideas? The downside I see is I will have to keep all these future values nullable? Maybe thats not a downside?
Also wold like to hear thoughts on how you generally cope with changes in database schema after deployment?

gideon
  • 19,329
  • 11
  • 72
  • 113

1 Answers1

4

No, it's actually a really bad idea. Either you foresee the use correctly in which case add them as they are meant to be, or you're just guessing at what might happen in which case you should wait until you know.

The way to handle schema changes after deployment is to change the schema (and any related code) after deployment. You should look into the acronym "YAGNI". In my opinion, any effort that is not needed immediately should be viewed as effort carried out for something that may never be needed. In other words, wasted effort.

If you have a unknown number of documents that may exist, that's a simple one-to-many relationship from the customers table to the documents table, with each document in the table carrying the document type and document payload, something like:

customers:
    custid  primary key
    <all other customer data>
documents:
    docid primary key
    custid references customers(custid)
    <all other document data>

That way, each customer can have as many documents as you wish, of as many types as you need.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • I kind of agree, but I think it's probably better to kind of "recognise" that you need flexibility in a way you didn't initial consider, and then potentially slightly redesign to allow for the 'reserved' structure you were considering. I don't think it's wrong to plan for the future (and I'm sure you don't either, but it's just not state clearly in your response). – Noon Silk Dec 19 '10 at 12:08
  • Not entirely sure what you mean by _add them as they are meant to be_ since I was told by the clients we maybe have upto 5 or 6 documents in the future (they dont have scanners so they don't really record all documents right now) – gideon Dec 19 '10 at 12:09
  • "with each document in the table carrying the document type and document payload" ... and the customer key ;) – Lazarus Dec 19 '10 at 12:12
  • @giddy: My advice to you is to restructure so you can support th eflexibility. Adding unused "fields" is a little weird, but having a relationship via foreign keys and a bridging table is very useful if the requirement is for "more then 1" of "X", where "X" in this case, is "document". – Noon Silk Dec 19 '10 at 12:13
  • 2
    If you want the flexibility to add documents without changing the schema, I'd put them all in one single table (they're probably all going to be BLOBs anyway). – paxdiablo Dec 19 '10 at 12:19
  • @pax they won't be BLOBs by guids (See question) or I was thinking file hashes (We just cannot put the docs into the DB, numerous reason...) – gideon Dec 19 '10 at 12:24
  • 2
    @giddy, not sure if hashes are any good. If you hash the document itself, how will you get the contents back? If you hash the document location, how will you locate it? You may want to think about just storing the location of the document (machine name plus full path spec, or something similar). – paxdiablo Dec 19 '10 at 12:26
  • @pax yep its an annoying situation. These documents are not high priority but basically when somebody in the office wants to bother auditing, they will get they're country-wide branches to submit dumps of all the documents and then the program will associate the documents with the record. – gideon Dec 19 '10 at 12:30