0

I am unsure if my solution in creating this db schema is as best practice. This involves 2 tables.

The table structure below is what I have currently. 

Table: tAttachmentType

Id    |    Name
1     |    Report
2     |   Screenshot
3     |    Other

Table: tAttachments

Id    |    File       |    CreatedDate    |    AttachmentTypeId    |    DeletedDate    |    Active    |    DeletedBy    |    UploadedBy    |    EngagementId    |    IssueId
1     | asd323sdfwe   |    01-01-2019     |          1             |    NULL           |       1      |    NULL         |    32            |    56              |     NULL
2     | kut456fdghh   |    01-01-2019     |          2             |    NULL           |       1      |    NULL         |    32            |    NULL            |     12345
3     | 345gvsdrfe4   |    01-01-2019     |          1             |    NULL           |       1      |    NULL         |    32            |    79              |     NULL
4     | nmrwe432gfd   |    01-01-2019     |          2             |    NULL           |       1      |    NULL         |    32            |    NULL            |     987645

Brief relationship in the tables. There can be multiple attachments for an issue OR an engagement. Every attachment must belong to one or the other. I am wondering about the EngagementId and the IssueId in the tAttachments table and whether that should be further normalized or is it okay to have null values like below.

If more info is needed, let me know.

philipxy
  • 14,416
  • 5
  • 32
  • 77
Jenga
  • 11
  • 4
  • "Normalized" means something, and it isn't "well-designed". Why is it in your post? "multiple attachments for an issue OR an engagement" is not clear, please clarify. (Including why "OR" is capitalized. Do you mean not both of something?) PS Radio button FKs to different tables for variants is an anti-pattern for DB/SQL subtypes/inheritance/polymorphism. https://stackoverflow.com/questions/21714715/designing-an-eav-database-correctly-for-historical-data – philipxy Aug 22 '19 at 19:45
  • Yes excuse me neglecting normalized, I am hoping to be at least 3NF as well as being well designed. "Or" is capitalized because it can only be one or the other. Also would you be able to help explain your point on anti-pattern? – Jenga Aug 22 '19 at 19:48
  • When clear this will likely be a faq. Before considering posting please always google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings, names & line numbers & with & without 'site:stackoverflow.com' and read many hits & answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Aug 22 '19 at 19:49
  • Please clarify via edits, not comments. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS Since "normalized" means what it means, if you have a question about it you should post one later when you have a design that you are going to normalize & show your steps following a reference. (One question per post please.) PS Re anti-pattern see the link in my previous comment & google the terms re what it is an anti-pattern for & re what you are doing expressed clearly etc per my other comment. Also re 2/many/multiple FKs to 2/many/multiple tables. – philipxy Aug 22 '19 at 20:00
  • 1
    Depending on the rdbms, a check constraint can be used to ensure that in each record, exactly one of the `EngagementId` / `IssueId` columns is null. This doesn't mean, however, that it's a good idea to use this design. Is there any kind of relationship between issue and engagement? – Zohar Peled Aug 22 '19 at 20:24
  • @ZoharPeled Evidently issue & engagement are (disjoint) types of attachment. – philipxy Aug 23 '19 at 09:44

0 Answers0