I have a few observations and suggestions:
Do not create a DATE
table when all you need is an element (column) for the date of the POST
. Generally speaking, a table with nothing but a meaningless ID and one other column is a good indication that you need to look hard at whether all you really need is the non-key column.
Consider CATEGORY
, this also has just its ID and the category description. Is this a good candidate for an independent table? There are two things to think about:
- Are there likely to be other attributes that depend on the category? In other words, should category be normalized out of posts?
- Is it possible that categories could exist that have zero posts? In other words, is the category list something that needs to be managed by an administrator or someone other than the person who creates posts?
On another subject:
Your model indicates that a POST
can be written by many USER
s. I'm not sure that this is really possible in your system, since you don't say anything about collaborative authorship. However, one thing that is almost certainly a problem is that each user can only ever author one post. I'm sure that's not what you meant.
To fix this you either need to move the foreign key in the relationship between USER
and POST
to the post side or you need to create an intersection entity if posts can have multiple authors.
Similarly...
Your business rules state that posts can have multiple CATEGORY
and TAG
assignments, however, your model doesn't reflect this. In your model each post can have exactly one of each of these.
To fix this you need to implement a many-to-many relationship for each of tags and categories. This will result in an intersection table in each case.
Lastly:
Why is some of your user information segregated into another table with a many-to-many relationship to the main user table? There may be good reasons to keep password information in a separate table from other user information, but I don't see any reason stated in your business rules. Also, it isn't likely that password will be many-to-many with your user record.
You're on the right track with splitting up your domains and using a linking entity. Separating the Film
s from the Star
s is absolutely the first step.
--------------------------- -------------------------
| FilmID | FilmName | | StarID | StarName |
|--------+------------------| |--------+----------------|
| EVIL01 | The Evil Dead | | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II | | 000002 | Sarah Berry |
| EVIL03 | Army of Darkness | | 000003 | Marcus Gilbert |
--------------------------- -------------------------
The missing piece is tying the two together. Obviously, stuffing the StarID
s into the Film
table is in violation of the first normal form, since you end up needing to store multiple references to actors in a single row.
--------------------------------------------
| FilmID | FilmName | StarIDs |
|--------+------------------+----------------|
| EVIL01 | The Evil Dead | 000001 |
| EVIL02 | Evil Dead II | 000001, 000002 | Wrong,
| EVIL03 | Army of Darkness | 000001, 000003 | Wrong
--------------------------------------------
Splitting the offending column out to its own table restores the integrity of the original Film
table, but continuing to store multiple references in a single attribute still invalidates 1NF for the data model.
--------------------------- -------------------------
| FilmID | FilmName | | FilmID | StarIDs |
|--------+------------------| |--------+----------------|
| EVIL01 | The Evil Dead | | EVIL01 | 000001 |
| EVIL02 | Evil Dead II | | EVIL02 | 000001, 000002 | Still
| EVIL03 | Army of Darkness | | EVIL03 | 000001, 000003 | Wrong
--------------------------- -------------------------
To rectify this is fairly simple, as you merely need to associate each FilmID
with the respective StarID
, bringing your model in-line with the tenants of 1NF.
--------------------------- ----------------- -------------------------
| FilmID | FilmName | | FilmID | StarID | | StarID | StarName |
|--------+------------------| |--------+--------| |--------+----------------|
| EVIL01 | The Evil Dead | | EVIL01 | 000001 | | 000001 | Bruce Campbell |
| EVIL02 | Evil Dead II | | EVIL02 | 000001 | | 000002 | Sarah Berry |
| EVIL03 | Army of Darkness | | EVIL02 | 000002 | | 000003 | Marcus Gilbert |
--------------------------- | EVIL03 | 000001 | -------------------------
| EVIL03 | 000003 |
-----------------
Now, with the the data normalized in this manner, it is easy to add attributes to expand the model. Say an actor's character name in a movie was desirable, you could add CharacterName
to the FilmStar
table - maybe useful for getting a list of everybody who's ever played "James Bond," for instance.
Best Answer
Yes, a bridge table (also known as junction table, mapping table etc.) is a perfectly sensible solution in this case, and it is common for a bridge table to have other attributes beside the references to the tables being connected.
As a stationary object, a stop is just a place, which has coordinates and may also have a fixed name as well as some other properties. As a part of a route, though, it can be assigned the additional attributes you have mentioned that would depend on the route, i.e. on the specific bus number. Those would need to be stored in a different table, the one that would link the stop and the route.
So, if by "bus" you mean "bus number" (or "bus route", "bus line"), then by all means create a dedicated table with references to
Bus
andStop
and with whatever additional attributes you deem necessary. Note, though, that ifBus
is meant to store information about specific buses (vehicles), then I would consider creating another entity (calledRoute
perhaps) that I would link to in the bridge table.Bus
would then probably reference that new entity, either directly or, again, through another bridge table (in case vehicles were allowed to work alternately on different lines).