We have 2 tables: FILES and FILEHISTORY defined like this:
FILES
( FILEID INT NOT NULL PRIMARY KEY,
FILEBODY LOB,
FILENAME VARCHAR)
FILEHISTORY
( FILEID INT NOT NULL PRIMARY KEY,
FILENAME VARCHAR,
some other fields)
That is, filehistory has extended properties saying what has been done to file with FILEID.
Now, our db guys made FILEID primary on FILEHISTORY table and FILEID from FILES table references FILEHISTORY's FILEID as a foreign key. Is that right? Shouldn't it 开发者_如何学运维be the other way around?
Filehistory should be:
FILEHISTORY
id int not null primary key
fileid int (foreign key to files)
other fields
filename can be removed from filehistory as it's already available in files, which is linked by fileid.
This model allows one FILES to have multiple FILEHISTORY
If you go by this design as proposed by your db guys, how does it take care of the fact that a file can have multiple history records?
For example:
File A - created yesterday by you
File A - changed by me today
In this case, based on the schema below :-
FILEHISTORY
( FILEID INT NOT NULL PRIMARY KEY,
FILENAME VARCHAR,
some other fields)
There will be 2 records in FileHistory for File A - say with FileID= 1 and FileID = 2
Questions -
Which one of the 2 FileID's (1 / 2 for File A) will be stored in "FileID" field in "Files" table?
In this design, using the ID will no longer be sufficient for searching the entire history of a file (Maybe thats why FileHISTORY also has a FileName field as thats the only real way of searching for the entire history of a file - and that wouldnt be as efficient as searching on an ID instead of a string)
Also, is it possible that the file can get renamed? If point 2 above is valid, then i guess that renaming a file will cause you to lose all history record for that file.
What I am trying to say here is that :
I think your approach of having the fileID as primary in FILES table is the better approach. I think it simulates the actual relationship of the files more realistically.
Depending on the requirements of your system, the proposed approach may work but it definitely will require more effort and there are some scenarios where it may potentially be more expensive / not be able to cater to.
精彩评论