Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How to model file status

    My users can upload files.

    I'm trying to model a file status, but I don't know whether to lump all the status' into one field or have separate tables.

    A file should be able to have more than one status.

    Some example statuses: user deleted, banned, disabled, uploading, pending.

    Some status', like banned, need to reference a banning reason, which are stored in another table (reason_id, reason_text).

    The two solutions I have come up with are:

    A

    Have an intersection table for files and statuses:

    files - file_id (pk) | user_id (fk) | file_name

    statuses - status_id (pk) | status_text

    file_statuses - file_id (fk) | status_id (fk) | value (optional e.g reason_id)

    B

    Have a separate table for each status

    files - file_id (pk) | user_id (fk) | file_name

    deleted_files - file_id (fk)

    banned_files - file_id (fk) | reason_id

    ... etc

    Which solution would you recommend? Or is there another design I've overlooked? The main problem is with storing the optional field (banning reason).

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    I think it would depend on how many different "reasons" tables you will have.

    If all your "reasons" could go into a single table, then I would opt for A.

    That way the field you labeled "value" really could change to reason_id FK reasons(reason_id)

    And I would see nothing wrong with also having an additional_info smalltext field for those occasions where one of the pre-canned reasons doesn't fit or isn't descriptive enough.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks that's helpful.

    In the near future I will need to store varied information for each status, along the lines of the following for example:

    Uploading - Start_time, started_by, server_location
    Pending - Start_time, waiting_for
    Suspended - Who suspended, time_of_ban, expires
    Banned - Who banned, ban_reason, time_of_ban, has_been_reviewed
    etc.

    Would you recommend B in this case? If so should I be concerned about speed?

    Because when I select a file in a regular use case i'd need to do

    Code:
    SELECT FROM FILES
    JOIN DELETED
    JOIN BANNED
    JOIN SUSPENDED
    JOIN UPLOADING
    ...etc
    Thanks.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,447
    Thanks
    76
    Thanked 4,372 Times in 4,337 Posts
    Actually, you probably would NOT like the results you would get from doing
    SELECT FROM FILES
    JOIN DELETED
    JOIN BANNED
    JOIN SUSPENDED
    JOIN UPLOADING

    First of all, those probably all need to be LEFT JOINs (after all, not all FILES will have any matches to DELETED, just to take one example).

    Secondly, consider what your results would look like.

    Let's take a simple case: File 1313 has two matches in DELETE and two matche in SUSPENDED, say.

    And let's say you do
    Code:
    SELECT file.filename, deleted.deleteReason, suspended.suspendReason
    FROM file 
    LEFT JOIN deleted ON file.fileid = deleted.fileid
    LEFT JOIN suspended ON file.fileid = suspended.fileid
    You will get *FOUR* records back:
    Code:
    framitz.txt   deletedreason1  suspendedreason1
    framitz.txt   deletedreason1  suspendedreason2
    framitz.txt   deletedreason2  suspendedreason1
    framitz.txt   deletedreason2  suspendedreason2
    And the more matches you have the more records you will get: SQL *must* (by the rules of cartesian joins) give you back *ALL POSSIBLE COMBINATIONS* of matches.

    I think, more than likely, you are going to end up deciding on option A.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •