0

I have a SQLite DB which stores log information for me. Sometimes some of the values could be editable, sometimes others are not editable.

To avoid having for each information two columns, I decided to create an extra table with two values to which the other table refers.

e.g.

LogDetail

  • Id
  • Value
  • Editable

LogTable

  • Id
  • FK_Timestamp
  • FK_User
  • FK_Titel
  • FK_Description

e.g. LogTable:

  • 1 1 2 3 4

LogDetail:

  • 1 "Timestamp1" True
  • 2 "User1" False
  • 3 "Titel1" True
  • 4 "Description1" True

Each of the FK_ entries is a foreign key which referred to LogDetail. Now I want to combine these informations to one row. There are many possibilities to achieve this. I tried this:

SELECT TimestampT.Value,
       TimestampT.Editable,
       UserT.Value,
       UserT.Editable,
       TitelT.Value,
       TitelT.Editable,
       DescrT.Value,
       DescrT.Editable
FROM Log,
     LogDetail AS TimestampT,
     LogDetail AS UserT,
     LogDetail AS TitelT,
     LogDetail AS DescrT
WHERE Log.FK_Timestamp == TimestampT.Id
  AND Log.FK_User == UserT.Id
  AND Log.FK_Titel == TitelT.Id
  AND Log.FK_Descr == DescrT.Id

Is it better to use multiple WHERE conditions or JOINS or something other? What is the most readable, what is the fastest in SQLite?

And if I get more columns, is there an easier solution for this problem then always add 4 lines for each columns?

Sir2B
  • 1,029
  • 1
  • 10
  • 17
  • 1
    This is not a well designed relational structure. You shouldn't use it. – itsLex Jul 23 '18 at 20:56
  • Comma means cross join. (With lower precedence that keyword joins.) DBMSs optimize queries. Just learn what joins & where do & write straightforward queries. Also it's time to read an textbook on information modeling & database design. – philipxy Jul 24 '18 at 00:07
  • Hi. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using use one variant search for your title & keywords for your tags. – philipxy Jul 24 '18 at 00:08
  • @itsLex why is not well designed? What would be a better structure? – Sir2B Jul 24 '18 at 05:46
  • @philipxy His question is about the difference between JOIN and WHERE. My question is which overall is the best solution for the problem. – Sir2B Jul 24 '18 at 05:56
  • You ask 4 questions, and the first 3 are answered at the duplicate. Please ask one question per post. PS Queries can be simpler if you give constraints including those declared in code. Please read & act on [mcve]. Explain what a row means when in each table. Why can't you use just one join of the 2 tables on id? – philipxy Jul 24 '18 at 06:25
  • As I wrote. Each column (except ID) consists of two values, one the real value (e.g. for timestamp the time) and one boolean information weather this value could be edit. If I had one entry in LogTable, I must have 4 entires in in LogDetail, which results in my query to result 8 values. – Sir2B Jul 24 '18 at 07:58
  • Re problems with your design: You have four subtypes of transactions. Google re sql/database subtypes/inheritance/polymorphism & its antipattern, multiple FKs to multiple tables, which you are using. (All faqs. To be found by googling many clear concise phrasings of your question/problem/goal.) PS Please clarify via post edits, not comments. Neither "as you wrote" nor your last comment are clear. Use enough words & sentences to clearly explain. Ask the (one) question you mean to ask. Google 'stackexchange notifications' to learn how to use @ to contact one previous non-poster commenter. – philipxy Jul 26 '18 at 11:00

0 Answers0