Reputation: 45921
Database design question
I have a story with lots of acts. These acts are not consecutive (I don't know which is first act or second, third, and so on).
Every story ends with a challenge.
To represent all in a relational database I have used the following tables:
Story
---------------------------------------
Id | PK
Name | String
FirstActId | FK to ACT table
Act
---------------------------------------
Id | PK
StoryId | FK to STORY table
Name | String
Description | Very long string
NextActionType | FK to ACTIONTYPE table
NextId | Its value depends on NextActionType
ActionType
---------------------------------------
Id | PK
Name | Values= 'Act', 'Challenge', 'Story'.
Challenge
---------------------------------------
Id | PK
Name | String
Description | Very long string
NextActionType | FK to ACTIONTYPE table
NextId | Its value depends on NextActionType
I use Story.FirstActId to know which is the first act for story (I'm trying to say that "Select MIN(Id) from Act where StoryId = ?" doesn't work.).
And then, I use Act.NextId to know which act or challenge goes after this act. Act.NextId can be an Act PK or a Challenge PK or a Story PK. This depends on Act.NextActionType value.
What do you think? Is it a good design?
My problem is I have Challenge table without any relationship with any other table.
UPDATE
In other words, I need a system to know where to go next. I will have the following situations:
story->act->challenge->act -> new story story->act->challente-> new story
NOTE: act can be more than one.
Upvotes: -1
Views: 111
Answers (4)
Reputation: 22187
The way I see it, the Challenge is simply a (sub)type of Act. Considering that the Act table contains all columns needed in Challenge, you can simply drop tables Challenge and ActionType. Use ThisActionType column instead of the NextActionType which could contain C,A if you need to know what type of Act it is (standard vs challenge).
Also get rid of the FirstActID from the Story table and find a way to identify first/last act in the Act table -- every act can belong to only one story in this model, and first and last are obviously special.
So, by the time it's done it would look something like this
ActPosition (F,L,A) would be used to identify first, last, any.
Upvotes: 0
Reputation: 191749
Your table design is not in 3NF and has the potential for update anomalies (for example, if the first act of a story is deleted the entire chain is broken; if its NextActId is updated, the chain changes completely). Another problem is that you must recursively seek on the chain to get all of the Acts for a story rather than getting them in one query.
If you want to be able to reuse acts between stories, you should use an indirection table:
StoriesActsMap
--------------
storyid
actid
ordinal_number
If you don't need to reuse acts between stories, then you can just stick with the Acts table:
Act
-------------
actid
actDescription, etc.
ordinal_number
storyid
The handling of challenges also depends.
Can a challenge also be an act? If so, you should store it in Acts and have an ActsType table:
Act
--------------
actid
acttypeid
...see rest of Act above
ActsType
--------------
acttypeid
acttype (Act, Challenge)
If a challenge is an act, is its definition as a Challenge obviated by its position in the story (last)? If so, the ActsType is not necessary, it would just be the MAX ordinal number on StoriesActMap/Act
If a Challenge is a Challenge and can never be an Act, it should be stored in separate table. You can either have a StoriesChallengeMap table if Challenges can be reused between stories, or just the Challenge table with a storyid key to map it to Stories if they cannot be reused. storyid would be a unique key on these tables since a Story only has one challenge.
Assuming that a Challenge is always a Challenge and Acts/Challenges cannot be shared between stories, the design would be as follows:
Story
----------
storyid
other info
Act
----------
actid
storyid
actorder (ordinal number of acts, (actorder, storyid) is a prime)
other info
Challenge
----------
chid
storyid
other info
Now you can get all of the Acts and the Challenge for a story in one query. Beware that a deletion of a story will orphan Acts/a Challenge if there are no foreign key constraints.
Upvotes: 1
Reputation: 2468
To me, Acts & Challenges seem to store the same kind of information, hence putting them in different tables seems overkill.
I'd probably go for..
Story
---------------------------------------
Id | PK
Name | String
FirstActId | FK to ACT table
Items (Acts or Challenges)
---------------------------------------
Id | PK
StoryId | FK to STORY table
Name | String
Description | Very long string
Order | Int - which position this Act / Challenge should go in
You could have a flag indicating if something is an Act or Challenge.
However, if I understand your description, then you could identify items by having a convention, e.g. setting the first item's Order as 0 making it the first Act, and when an Act / Challenge is the last item in the set (ie Max(Order)) denoting it as a Challenge.
Upvotes: 0
Reputation: 5661
I would change some things:
Act
---------------------------------------
...
NextAct | FK to Act table, can be NULL
Challenge | FK to Challenge table, can be NULL
No need of ActionType
You will save space. An act is followed by an Act if NextAct is not null, an Act is followed by a challenge if Challenge is not null.
Of course, the condition (NextAct xor Challenge) must always be true. You can assert this by adding a table constraint on insertions in Act table.
If you have a lot of type of next actions (since we only have two actions here, it is easy), you will need to look at the problem in a different way: "a challenge has one Act", "an Act can have one Act" "ActionN has on act"
Like that, you will be able to find the next action as below:
SELECT
challenge_id,
act_id,
...
FROM
Act
LEFT OUTER JOIN
Act NextAct ON Act.next_act_id = NextAct.act_id
...
LEFT OUTER JOIN
Challenge ON Act.challenge_id = Challenge_id
At least one will not be NULL.
Upvotes: 0