-2

I have an application in which I store PostId and keywords (Keyword) belonging to a Post in a table named KeywordsForPost. The primary key for that table is the combination of PostId and Keyword. PostId is not unique nor is Keyword.

I needed this implementation because I might need to search for posts regarding the keywords they contain.

I have another table named NewKeywords. The one and only column in that table is Keyword. When a post is created, keywords in that post are inserted into both KeywordsForPost and NewKeywords tables. An operation is applied to the keywords in the table NewKeywords at the user's command so that they no longer become "New keywords". So I delete those keywords after that operation is applied. Currently my NewKeywords table does not have a relationship with any other table. Is this practice justified? Or is there a better practice?

I searched and found this answer. can we have a table without any relation with the other tables
But did not find it satisfactory. I also find it different to the question previously asked because it asks a general question, whereas mine is specific. I need to know if a relationship can be added to the table. So far I came up with nothing.

Community
  • 1
  • 1
Lilylakshi
  • 736
  • 7
  • 19
  • 4
    What did you find unsatisfactory about that answer? – TZHX Oct 26 '15 at 17:59
  • As all the answers indicate in the question you provided - yes, this is perfectly fine to do. Just because you're working in a relational database does not mean that *all* tables *must* be related to at least one other. – Siyual Oct 26 '15 at 18:04
  • 1
    For what it is worth the "Relational" in "Relational Database" does not actually refer to the relationships between tables (foreign keys), it refers to the related data with a single row within a table. For example a customer table might have forename, surname, date of birth and address. These separate items are related because they refer to the same customer. – GarethD Oct 26 '15 at 18:08
  • 1
    Why not having a Keyword table with a flag of "New", and using the key of that table as foreign key of KeywordsForPost? – rlartiga Oct 26 '15 at 18:30
  • @TZHX Because it says a relationship can be added later if I wanted. I wanted to know if a good relationship existed in my case. – Lilylakshi Oct 26 '15 at 19:26
  • in case you want to do this by the books you can check this: https://en.wikipedia.org/wiki/Database_normalization it can be a bit fuzzy but these guidelines can help to a certain degree. However personally, after the 4th it gets overly complicated so i usually stop there unless necessary. – ThrowingDwarf Mar 11 '16 at 13:43

3 Answers3

0

Yes you can. The only thing that could happen is that those table wouldn't have a relationship to other table. i would not say that this is the best way to go, because all depend in your situation. And, like the answer says: It can still be given a relationship later.

Maduro
  • 713
  • 5
  • 23
  • 44
  • That's what I am interested about. If it can be given relationships later, what are they? I have done some head scratching and came up with nothing. – Lilylakshi Oct 26 '15 at 18:24
  • Hi @LilyLakshi , the first thing is try to understand database relationship review this site and let me know if you have more questions i will be waiting here =) http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 – Maduro Oct 26 '15 at 18:29
  • @LilyLakshi take a look to this correct answer that work with the `ALTER TABLE` http://stackoverflow.com/questions/1308339/create-a-one-to-many-relationship-using-sql-server – Maduro Oct 26 '15 at 18:33
0

Either I'm misreading your question, or there actually is a relationship between NewKeyWords and KeyWordsForPost. It's a value (Keyword) that's common to both tables, and could be used for a relational join. That might be a stupid join that no one would want to do, it might be real slow, for lack of a relevant index, and the keywords aren't a declared key anywhere, but it's still a relationship.

The relationship is inherent in the data, whether you have declared it or not.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Yes. I understand a join can be made. I was interested to see if there can be a relationship that would enforce referential integrity of some sort. For an example `NewKeywords` should not have a value that is not present in `KeywordsForPost`. Thanks for your response! – Lilylakshi Oct 26 '15 at 19:23
  • Any two tables can be joined. You do not need a FK between them. The meaning of a JOIN is the AND of its arguments' meaning. A "relationship" in the sense of FK is just the *fact* that every referencing subrow appears as a referenced subrow. @LilyLakshi If that isn't true then there just isn't a FK. But "NewKeywords should not have a value that is not present in KeywordsForPost" (if you update the latter first). Hence "The relationship is inherent in the data, whether you have declared it or not." – philipxy Oct 27 '15 at 09:52
  • Fair enough. Any two tables can be joined with a cartesian join. I meant an equijoin, but I didn't phrase it carefully. My point about the existence of a relationship was in reference to a presumed ER model of the subject matter, and not in reference to a relational data model of the database design. In the wording I'm accustomed to, an undeclared FK is still an FK. – Walter Mitty Oct 27 '15 at 10:49
  • @Lily, are you concerned about orphaned references? If so, enforce RI. If not, don't worry about it. – Walter Mitty Oct 27 '15 at 10:52
  • @WalterMitty Seeing that table on its own didn't feel right. Yes I wanted to enforce RI. But studying this for bit has made me convinced that it is not that big of a deal in this particular case. Thanks for your response! – Lilylakshi Oct 27 '15 at 11:04
  • @ WalterMitty Re ER: Yes it's clear that that was your context. I meant to put the "@LilyLakshi" a sentence earlier. RE Join: [Cartesian Join is just Join with restricted inputs](http://stackoverflow.com/a/25957600/3404097), whether Natural Join or Equijoin (per the kind of relations/operators one is using). Eg SQL FROM always conceptually calculates a Cartesian Join. SQL just restricts syntax CROSS JOIN to have no ON and (INNER) JOIN to have an ON. – philipxy Oct 27 '15 at 12:14
  • @philipxy. when you said "if you update the latter first" you might have added "or in the context of a single atomic transaction". But I understand that you're trying to to throw in too much complexity. – Walter Mitty Oct 27 '15 at 12:26
0

I am going to take rlartiga's approach from a comment:

Why not having a Keyword table with a flag of "New", and using the key of that table as foreign key of KeywordsForPost?

I am going to create a Keywords table with the column Keyword and have it as the primary key. Then I am going to have both KeywordsForPost and NewKeywords tables refer to Keyword in Keywords.

philipxy
  • 14,867
  • 6
  • 39
  • 83
Lilylakshi
  • 736
  • 7
  • 19