What would my SQL statement be to insert "Arnold Schwarzenegger" and "Hasta la vista baby" into these two empty SQL tables?

Question

What would my statement be to insert "Arnold Schwarzenegger" and "Hasta la vista baby" into the following empty SQL tables?

The title of this question was originally going to be "How to insert the first records into SQL tables with bidirectional associations and autogenerated integer PK?" but I wasn't sure if I was phrasing that correctly... Basically, I have two tables Actors and CatchPhrases.

Actors looks like:

  ActorId                  int             NOT NULL  PK (autogenerated by db)
  FavoriteCatchPhraseId    int             NOT NULL  FK
  Name                     varchar(200)    NOT NULL

CatchPhrases looks like:

  CatchPhraseId            int             NOT NULL  PK (autogenerated by db)
  ActorId                  int             NOT NULL  FK
  PhraseText               varchar(500)    NOT NULL

So, Actors can have multiple catch phrases but must have at least one. A catch phrase is associated with an actor. There is currently no data in either table.

tvanfosson · Accepted Answer · 2011-03-05T18:31:40.567

5

I would model it differently to avoid a bidirectional relation (which would be difficult to do). Simply add a column (IsFavorite) to the CatchPhrases table. Either use a constraint or business rule in code to limit the number of catch phrases marked as a favorite for each actor to one.

Actors:

ActorId                  int             NOT NULL  PK (autogenerated by db)
Name                     varchar(200)    NOT NULL

CatchPhrases:

CatchPhraseId            int             NOT NULL  PK (autogenerated by db)
ActorId                  int             NOT NULL  FK
PhraseText               varchar(500)    NOT NULL
IsFavorite               bit             NOT NULL

Make sure that you have an index on ActorId for the CatchPhrases table so you can quickly find the actor's catch phrases.

Alternatively, using a join table -- which would allow multiple actors to have the same catch phrase.

Actors:

ActorId                  int             NOT NULL PK (autogenerated by db)
Name                     varchar(200)    NOT NULL

ActorCatchPhrases

ActorId                  int             NOT NULL PK (FK to Actors)
CatchPhraseId            int             NOT NULL PK (FK to CatchPhrases)
IsFavorite               bit             NOT NULL

CatchPhrases

PhraseId                 int             NOT NULL PK (autogenerated by db)  
PhraseText               varchar(500)    NOT NULL

edited Mar 05 '11 at 18:31

answered Mar 05 '11 at 17:35

tvanfosson

524,688
99
697
795

Are you saying it is not possible to do as it is? It seems to me that marking the record IsFavorite in a bit field could (1) potentially result in multiple favorites. As you noted this can be avoided in a business layer, but also (2) it doesn't change anything about the catch phrase when it becomes a favorite, it changes something about the actor, so it seems like the actor table should be the one that is updated. – smartcaveman Mar 05 '11 at 17:36
@smart - I'm not a SQL guru, but I don't think you can do it with the tables defined as you have them. You'd need to make one of the FKs nullable and not enforce the constraint so that you could insert into one table, then insert into the other (and get the id), then update the first with the id from the second. I think it's simpler my way. – tvanfosson Mar 05 '11 at 17:42
Both solutions had occurred to me. I'm going to wait and see if someone can come up with a way to do it, but otherwise I will select you. Thanks for your help. – smartcaveman Mar 05 '11 at 17:44
@smart - I see your point, but you could argue it either way. You could still have "Favorite" property on the Actor in your code, but model it more simply in the DB. FWIW, what would you do if you decided that you wanted to model the actor's Top Ten favorite phrases? I'd do it by having a rank associated with the phrase, not by having 10 FKs for the actor. Also, you might want to consider that multiple actors could have the same catch phrase -- it happens. In that case, you'd use a join table (and the favorite/ranking) would migrate there. – tvanfosson Mar 05 '11 at 17:47
In that case, I would create a separate association table that includes a nullable rank field. I also, wouldn't treat that actors with the same catch phrases as a single catch phrase, because, while the text is the same they are different, and the potential associations that could arise might not be friendly to treating them as a single catch phrase. I think the structure of the data in the database should at least aim to reflect the structure of the business objects. – smartcaveman Mar 05 '11 at 17:52
@smart - it might help to think about it from a language perspective. You say "An actor has a favorite phrase." In this case, favorite is an adjective modifying the noun "phrase", i.e., it a quality of the phrase, not the actor. – tvanfosson Mar 05 '11 at 17:52
@tvanfosson, But that's not what it means to say something is your favorite x. If my favorite beer is heineken, then it doesn't follow that Heineken is DEFINED as a favorite beer. Favorite is an attribute to the association between myself and Heineken. It's equivalent to saying "I have an attitude of favoriteness with respect to the beer that is named Heineken". I see what you're saying, and in this case it actually does make sense, because I have modeled "Catch Phrase" an attribute of an Actor and not an independent entity. So the Table should probably be called ActorCatchPhrases – smartcaveman Mar 05 '11 at 17:56
@smart - I would tend to agree that the models should be similar, but a relational model isn't exactly the same as your business model - for example many-to-many relationships involve a join table. If you can greatly simplify the db model without compromising the business model by modeling the relationship in a way that's natural for the DB, I think you should. – tvanfosson Mar 05 '11 at 18:01
@smart - that's where I was going with the ranking idea. It's really a property of the association, but the way you've defined the problem the association is most naturally carried in the CatchPhrases table. You might also want to consider that under your schema it's perfectly legal for an actor to have a favorite phrase that actually belongs to another actor. I'm not sure that's what you are intending. – tvanfosson Mar 05 '11 at 18:03
@tvanfosson, I think you are right conceptually, but my solution would still not be to put the field on the CatchPhrases, but to create a third intermediary table for the association, which has the qualifying bit field IsFavorite. – smartcaveman Mar 05 '11 at 18:08
@smart - sounds good. Then, when you get to the point where you're convinced that when two people say the same thing, that thing is represented by the same row in the phrases table, you won't have to change the design. #normalization – tvanfosson Mar 05 '11 at 18:14
@tvanfosson, I don't think I'll become convinced of that. For example, consider that the application develops and a column needs to be added to include a URL of an audio sample of the actor saying the catch phrase... See where I'm going? I think it makes more sense to query by the text if for some reason you need to get all the instances of a catch phrase having a certain text representation, as opposed to assuming they are the same and potentially getting unpredictable results elsewhere. – smartcaveman Mar 05 '11 at 18:30
@smart - that goes on the join table because it's part of the relationship between the actor and the phrase or, more likely, in an audio snippets table that is associated with the join (which probably means that it ought to have an autogenerated, artificial key itself). – tvanfosson Mar 05 '11 at 18:34
@tvanfosson, I've written 10 comment responses, seen the error and then subsequently deleted them. This makes me think that, (at least given the paradigm that we have used to approach the associations), you are right and I am wrong, (although there are alternative paradigms for which the opposite would hold true). Congrats – smartcaveman Mar 05 '11 at 18:48

score 2 · Answer 2 · answered Mar 05 '11 at 19:53

The rule, namely that the parent record must have at least one child record, cannot be enforced with declarative referential integrity.

"Favorite" is a singular, and therefore FavoriteCatchPhrase could simply be an attribute of the Actor entity, i.e. a column in the Actors table. You could store the text of the phrase. But if you wanted to enforce the rule that the favorite catch phrase must come from a set of bona-fide catch phrases, phrases that have been vetted and acknowledged to be truly "catchy" and not merely some not-so-memorable saying, then you would have a CatchPhrases table and you could have Actor.FavoriteCatchPhrase store the phrase id and reference CatchPhrases table as a foreign key, though more than one actor could use the same catchphrase unless you put a unique index on Actor.FavoriteCatchPhrase.

Thank you Tim. I think you give a concise explanation of the problem. — smartcaveman, Mar 05 '11 at 20:10

amelvin · Answer 3 · 2011-03-05T18:37:50.720

I think you can do this within a transaction, by using a 'dirty read' (read uncommitted).

But its not very nice even if possible.

As @tvanfosson suggested the cleanest way would be to turn off the FK on ActorId column of the CatchPhrases table.

First create a dummy CatchPhrases (set identity insert on) row like:

0 - 0 - 'No favourite catchphrase', 0

Then when you want to insert an Actors row there is a default to use:

(identity) - 0 - 'Arnold Shwarzenegger'

Then set a variable to the @@identity value that the Actors insert will have generated

Then the catchphrase:

(identity) - (variable) - 'Hasta la vista baby'

Then set a variable to the @@identity value that the CatchPhrases insert will have generated and use it to update the catchphrase id in the actors row.

... phew, are you sure this design is right?

EDIT

Well if we can change the design ...

Looking at the relationships, Actors can have many Catchphrases and Catchphrases can have many Actors. So there is a many-to-many design - which is usually refactored using a link entity (MSDN calls this a junction table):

Actors   
  |
-----
| | |
ActorsCatchphrases
| | |
-----
  |
Catchphrases

Actors has the Actor Name and Actor details (with no references to catchphrases)
ActorsCatchphrases has the ActorId and the CatchphraseId and the boolean as to whether it is the favourite of that actor
Catchphrases has the details of the catchphrase (with no reference to an actor)

I said the design was wrong, I didn't say you could change it. - Your design is certainly better than what I initially proposed, but my question was to find out how to do it with a design that was apparently fundamentally flawed to begin with. Thanks! — smartcaveman, Mar 05 '11 at 18:52

What would my SQL statement be to insert "Arnold Schwarzenegger" and "Hasta la vista baby" into these two empty SQL tables?

3 Answers3