One thing jumps out here:
The entire process uses the same set of login credentials
Problem
So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.
However, you lose ownership chaining like this.
Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.
Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well
Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.
The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"
Solution?
If the extra databases are code containers only, then schemas are a better idea.
This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database
With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases
For more on schemas, see these questions:
Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this:
Decision criteria on when to use a non-dbo schema vs a new Database
No, you shouldn't merge the 3 tables.
- For a given flight, aircraft may change
- For a given aircraft, flight may change
However, I'd suggest that your model isn't complex enough
- FlightSeats depends on Aircraft model (seat layout etc)
- Aircraft departs Airport too...
- .. but Flights leave and departs Airports. Or is it FlightAirport?
- Flight is arguably 2 entities: the base flight (number, schedule etc) and the actual FlightInstance (date etc)
Have you written down all your facts first? Say in tabular format like this MSDN example (actually for ORM which I use because ERDs don't capture all the information you need)
Edit, more...
- As per @ypercube and @Simon Righart comments below
- From @Simon Righart: A given aircraft might have three different classes of seats (economy, business + first) with different numbers of each each flight number might be flown by multiple aircraft
Best Answer
I think you could do this with three tables and a view, User, Bookmark, Story_View and Story. User would contain all the users (log in information and an ID).
Story would contain a field in which you could put whatever the story is then when you want another user to modify that story you could just select that story and make a new record adding on whatever the next user adds on. Also it would have a number primary key as well as a foreign key to user ID so we can select the original owner of the story.
Story View would be what the users actually see. Users would only be able to see the data for stories they have made or are able to write on. There's definitely a better way of doing this using something outside of SQL.
Bookmarks table would simply store a foreign key to both user and story every time a user adds something to their bookmarks a record would be added to the bookmark table containing the user Id and story id so you could easily use a user ID to select all that users bookmarks.
I'm not going to draw a diagram because I think it's pretty easy best on this design.