This implementation does have a weakness - it allows to store teams Columns that apply for football teams only The base design is like this: - One table for "supertype" This is known as a subtype/supertype situation. Seperate tables for the attributes and relationships specific to each Or you could useĪ surrogate, but in that case, what would it be a surrogate for? Iĭon't consider either solution very satisfactory.Ģ: Even without the missing PRIMARY KEY, I don't think this reallyĪ much better way would bew to introduce a geenric teams table for allĪttributes and relationships that apply to all types of teams, with You could useĬOALESCE to create a computed columns out of the three foreign keyĬolumns, then use that + PersonID as the primary key. + CASE WHEN BaseballTeamID IS NOT NULL THEN 1 ELSE 0 END)įOREIGN KEY (PersonID) REFERENCES Persons,įOREIGN KEY (FootballTeamID) REFERENCES FootballTeams,įOREIGN KEY (BasketballTeamID) REFERENCES BasketballTeams,įOREIGN KEY (BaseballTeamID) REFERENCES BaseballTeamsīut I would not recommend this, as it has some serious flaws:ġ: There is no obvious candidate for the primary key. + CASE WHEN BasketballTeamID IS NOT NULL THEN 1 ELSE 0 END One possible solution is to design the many-to-many table (which I've also renamed to something more English-like) as follows: CREATE TABLE FavTeamsĬHECK ((CASE WHEN FootballTeamID IS NOT NULL THEN 1 ELSE 0 END This is only one side of the problem, I'm only trying prevent inconsistent data INSERT, the next step is to prevent REMOVAL of the used items. Oh, I tried putting the logic in a user define function but UDF s are not welcome either, only system defined functions may be used. Is there any other methods that I can try? Is there any other way of writing such Check Constraint? WAIT !! Sub-queries aren't allowed in Check Constraints, I get an error saying:"Subqueries are not allowed in this context. OR (TeamType = "Basketball" AND TeamID IN (Select ID from BasketballTeams)) OR (TeamType = "Baseball" AND TeamID IN (Select ID CHECK ( (TeamType = "Football" AND TeamID IN (Select ID from FootballTeams)) So I've created (attempted to create) a check constraint that looks like : Now I would like to create my Foreign Keys which will be referring to different tables based on TeamType. I have only one table to manage the relations. So I decided to re-consider my DB design. (my real case get's a lot worse than that: each team may participate in many leagues and there'll be many teams in a league) People FootballTeams BasketballTeams BaseballTeams People_FootballTeam_Relation People_BasketballTeam_Relation People_BaseballTeam_Relation So I ended up creating one table plus an in-between table for each different Sport Teams and you can imagine how my DB looks like: While some of theįields shouldn't even allow NULL values for one team type, the other types don't need that field at all. To make it more complicated I have different type of teams (Football team, Basketball team, Baseball team) and unfortunately there is no way of keeping all those team within one table,thats because they requires different data fields. So I have 3 tables: People, Team, People_Team_Relations and I can establish the data integrity by only two foreign keys. I directly start thinking of creating an in-between table to provide many to many relation, you agree right? Lets say I'm designing a database in which I'll store Favorite Teams for individuals.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |