You are on a good way and for a first work with RDB you did it quite nice. But there are some points you need to think about resp. to recognize also some consequences.
Keep in mind, it is not possible to give you a final answer, whether your layout is ok or not without knowing more about your final target. But it is possible to highlight some points to give you an idea about the consequences of the design. So, let's go through it, table by table.For the following I assume that all fields
*ID have the data type int.
[Users]
UID (pri)
Probably an internal auto generated key, defined as primary used to manage realations. Usually the end user will never get in contact with this value. A widely used technics, btw. I do it also always like this.
Username
At the moment no contstraint recognizable from the source given by you. But think about what confusion comes up if two person will use the same Username.
-> So most probably you will apply a unique constraint for
UserName.
Discussion:
As you can see, introducing a "internal numeric primary" will force us to introduce also something like a "user primary" which leads to
one index more. Don't worry about that, it is really a widely used technics.
Note:
If you do not pay attention to the technical solution and design your database layout
strictly based on the theory, then "Username" will be the real primary key. With the consequence that also foreign keys becomes the same type...
[Files]
FID (pri)
Same as UID above, ok.
Filename
Same as above with UserName, but maybe not that critical. Personally I would also apply a unique constraint for
Filename>
[UserFiles]
Lets first have a look to the more easy part:
UserFiles.UID
should be defined as Foreign Key to
Users.UID. Here you should think about to also include a "ON DELETE CASCADE" in the foreign key definition. That means, in case you delete a User, all entries in UserFiles will also be deleted for the respective user.
"ON DELETE CASCADE" is cool, we do not have to bother about to delete the details in UserFiles. Hmmm....
really cool?
In some situation it is great, but is it also here for your layout? Only you can decide it. What is the point: In case you allow/need to delete a User it means you need to delete the UserFiles (in case you make UserFiles.UID a foreign key). But finally that means also deleting the corresponding UserFiles
removes also the information, which files have been uploaded by which user. Again, it's up to you to decide whether you can live with that.
UserFiles.FID
should be defined as Foreign Key to
Files.FID. Don't apply a "ON DELETE CASCADE" for this foreign key, otherwhise all Files related to a deleted user will also be deleted in case they are not used by other users ;)
Now the harder part:
At the moment you defined a sequence of the three fields
UserFilesID, UID, FID as the Primary key for UserFiles. But was is really the primary?
V1.) On a first glance it should be
UID, FID. But like this, a user can only either upload or download a file.
V2.) Let's the user allow to upload a file and later download it. That means the primary needs to be like
UID,FID,Operation. But like this, the user can download a file only one time...
-> V1 and V2 don't makes sense, let's forget them.
So what now? For this we need to define what we allow a user to do.
a.) Can a user download a file more than one time?
b.) Can a user upload a file more than one time?
The answers to the above question will help to decide how to go on. So, I would decide like this:
a.) Yes, a user can download a file several times. But then we need one other field more to make a primary, i.e. a counter or a datetime.
b.) The same file can't be uploaded more than one time with same FileName.
-> Stop here... this two aspects we will not link to the question of primary key for UserFiles.
After all above I would make UserFiles _at the moment_ like this:
CRATE TABLE UserFiles
(
UserFilesID integer not null,
UID integer not null,
FID integer not null,
Operation nchar...,
LogDate datetime,
CONSTRAINT PK_UserFiles PRIMARY KEY (UserFilesID),
CONSTRAINT FK_UserFiles_Users FOREIGN KEY (UID) REFERENCES Users (UID)
ON DELETE CASCADE,
CONSTRAINT FK_UserFiles_Files FOREIGN KEY (FID) REFERENCES Files (FID)
)
Abstract:
Q1: Is my DB design right?
Q2: Is this a correct logic? or what I should expect?
See above discussion and some details you need to decide.
Q3: How can I check if my design is correct? I'm thinking a select statement that would check the uploads of user with UID=22 and the result is files with FID = 10,11,12
That is one test, more will be needed. Ask yourself what you like to show and then make the SQL for it and test it and compare the result against your expected result.
Q4: What select statement should I use?
SELECT
Users.UID,
Users.UserName,
Users.RealName,
UserFiles.UserFilesID,
UserFiles.UID
UserFiles.FID
UserFiles.Operation,
Files.FileName,
Files.Description
FROM Users
LEFT JOIN UserFiles ON UserFiles.UID = Users.UID
LEFT JOIN Files ON Files.FID = UserFiles.FID
WHERE Users.UID = @UID
ORDER BY ....
A hint for Foreign Key Constraints:
Check with your choosen SQL Server, whether defining a foreign key does also creates an non unique index for it. This becomes important when your database becomes big. I.e. MSSQL does not create an index for a Foreign Key Constraint and in case you have a detail table with hundred of thousands records this becomes usually a bottleneck.
Good luck, and I hope it helps.
Submit an article or tip