If you feel like a little dogfood - pickup SQL Express.
Forum Read Only
This forum has been made read only by the site admins. No new threads or comments can be added.
This approach sounds a lot like the open source cocoon for java. You might want to check that out.
hey kenfine, how do you implement the "join" stuff?
I'm totally revamping my DB design and actually making it relational for once
I'm having problem establishing relationships. Say if I wanted to allow for more authors, what should my tables look like? What would be the primary keys/foreign keys, etc?
Yeah, I'm not very good when it comes to databases.
There are three canonical joins to remember:
Extend the table by adding fields, no need for a join
Create a foreign key from the joining field on the "many" side to the primary key on the "one" side.
Create an interstitial table with a composite key. Create two foreign keys from this interstitial table to each of the tables you're joining.
Book to Title is a one-to-one join. Create a Title field in the Books table.
Author to Quote is a one-to-many join. One author, many quotes. Create an AuthorID field in the Quotes table and a foreign key from Quotes.AuthorID to Authors.AuthorID.
Book to Author is a many-to-many join, as you wish to implement it. Create an interstitial BookAuthors table, with at least the two fields BookID and AuthorID. Create a composite primary key on BookAuthors of (BookID, AuthorID). Create foreign keys:
BookAuthors.BookID to Books.BookID
BookAuthors.AuthorID to Authors.AuthorID
Then add rows to the BookAuthors table to note which books where written by which authors. If a book has multiple authors, you can add multiple rows to that table for that book.
There are other methods - you might have fields like Books.PrimaryAuthorID and then have a BookSecondaryAuthors table. Or you might try to squeeze all the AuthorID's into a comma-delimited field of the Books table. But I like the first solution as it's a bit more key-friendly.
I don't see how that could work...
t_categories shouldn't have a cPageID column, should it?
I would do t_webpage_authors as:
PK, FK cPageID
PK, FK cAuthorID
note the composite PK
cPageID FK is to t_webpages.cPageID
cAuthorID FK is to t_authors.cAuthorID
similarly for t_webpage_categories
Not sure what t_webpages.cAuthors would hold?
Perhaps instead of t_webpage_authors you might make cAuthorID a field of t_updateDates?
Getting into objects and classes and OOP is a close second.
So I'd encourage you to sit with enough books and practice projects to get all of the relational database stuff down cold. Give yourself different projects with different requirements: your website, or organizing your collection of, uh, downloads, or anything else that involves storing a bunch of real-world entities.
With the 33 year-old geezer done with his preaching, I'll move on to your question. If you're new to it, joins are going to be tedious and confusing to code. I suggest using a tool like the SQL Server Query Builder or something analogous to help you construct multi-table queries: you'll burn a lot of time on syntax issues otherwise.
The other folks have explained issues involving "multiples" well. Just recapping it briefly, you're going to create three tables, say,
Con_ContentID (Primary Key)
Per_PersonCategoryID [value will correspond to "authors"
ConPerID (PK) [not strictly necessary but you'll find it handy...]
[this is a "join" table that allows for the "many to many" relationships"
The relationships between Contentitems and Persons(author) are stored on the join table, Contentitems_Persons.
If you construct a query properly, you can get both content information, like the Title, and author information, like the name, in the context of a single query. This can be used to build data access structures like ADO recordsets or their .NET equivalents.
In cases where there are multiple authors linked, it is possible in ASP/ADO or ASP/ADO.NET to build hiearchical data structures that grabs all of the "children" of a parent item -- for example, all of the authors associated with a contentitem.
Getting to a place where you understand all of the code stuff and database stuff and tools stuff that you need to grok this can take some time, so study some good DB designs and be patient with yourself.
When I designed my first big, properly relational database, I remember looking carefully at this example:
All of the comments from the "peanut gallery" as this poor guy tries to write his article are instructive... people have a lot of good points, but at some point you have to accept that every design is a compromise and implement something that will do the job at hand.
Try to develop something reasonably flexible, and properly relational, but don't get too carried away. On one of my first projects I designed a 50+ table system, and that is too many. It's tricky in that there are a lot of self-appointed "experts" in academia and elsewhere who are eager to pounce on "inferior" or "insufficiently normalized" databases, and there's a good lesson somewhere in what they're saying. But if you aren't careful you'll end up with a system that is so complex that your dev efforts will slow to mollasses.
Until you learn about OO and classes, that is.
Have fun with it.
An update on my original posting in March, and the status of this project.
It has been a very cool ride and it feels like my learning and skills have been growing at a breakneck pace for the last eight months. I spent ten days in March attending the FlashForward conference, and when I wasn't Flashing, I read Connelly's classic Code Complete. That gave me a lot of ideas and best practices to think about, and the code I wrote when I came home was approximately 100% better than when I had left ten days earlier.
When I came home I programmed a comprehensive collection of classes in Classic ASP. The design is well-thought out in that the objects behave in appropriately object-ey and modular ways. Things are granularized nicely into realms of resposibilities just like Shalloway and Trott tell us to do. Objects call other objects, pass packages back and forth, send and receive messages. Code for complex content-management and creation activities (e.g. making Flash galleries, generating PDFs, sending stuff, archiving content appopriately, generating and rendering nav from a DB ) are all stashed away in classes, simplifying my work dramatically. My non-tech bosses are very happy with the speed and flexibility with which you can extend the system. Crowd goes wild.
So then I started thinking about the best way to limit code complexity as we render to different device-types:
All the whizzes here pointed me in the right direction. Guess all of those fancy OO toys were invented for a reason.
Around the same time I installed VS.NET 2005 beta2 and started playing with the Class Designer. In ten minutes the world changed. Killer development and documentation solution. At this point the plan is to migrate everything to C# and ASP.NET 2.0. I've already united all of our applications to a single relational data store. Since I also maintain our websites and webapps, and since both my databases and object models are getting pretty complicated, ASP.NET's dynamic caching abilities look like a dream given our needs.
The original goal of my content/template management scheme was to maximize flexibility and capability. A lot of these best practices are anticipated and integrated into ASP.NET already, or can be with a little work.
Content management issues are tricky, in that a lot of what you do right comes out of a deep understanding of your data, existing work processes, and how that translates into an effective database/system design. Ain't gonna cram that into Content Management for Dummies.
Will keep fellow Sandboxionians advised as I learn more. MSFT's Tech Ed is in a week, and I'm going. Wonder if I'll be refactoring my plans again when that's all over.
Yeah, I really do need to learn some more about databases and SQL. Before I was just using a database as just a container.
Have any recommendations for Database books? I'm thinking of getting Code Complete ad Object Thinking as suggested by Sampy a long time ago.
Anyways, does this make sense:
Btw, your CMS looks cool and I can't wait to see it live in action!
mVPstar, your schema looks pretty good. Think carefully about naming issues: I'd avoid prefixing the elements with "c" as you're doing in the diagram, or "t_".
Also think long and hard about what things like "CategoryID" really mean. What are you describing?
You have table "webpages". Depending on the scope of your project and its future growth, it may be sufficient. Or it may not, and you'll be forced to add tables.
One way I fought off "table spawn" in my own DB design was changing my original "webpages" or "articles" table into "Contentitems", which is a much more abstract description. Many different content-types can be encoded with this structure: webpages, photos, related links, related archival content, Flash galleries, the individual elements of Flash galleries, etc.
Just some things to think about. The more abstract you make things, the more you'll have to code, but the more flexible your design will be. Learning classes will help you manage the increased complexity.
Stuff to read:
Code Complete, Second Edition, absolutely.
Professional SQL Server 2000, Wrox Press.
While you're in the bookstore, thumb through Database Design for Mere Mortals. Not worth buying if you're at all familiar with DB design concepts.
Read the "Books Online" part of SQL Server.
Study the examples I've suggested earlier on ASP101.com, they'll really help.
The Dreamweaver MX Cookbook has some simple examples of multi-table DBs supporting web applications.
When you're ready to take on OOP and classes, read Scott Mitchell's Designing ASP Pages, Shalloway and Trott's Design Patterns Explained, and maybe Wan and Hall's Object Oriented Actionscript. The class stuff is one thing that I really wish that I had found a year or two earlier than I did.
That should get you started. Have fun.
The categories table is just there if a webpage fits into more than one category and there are fized categories. I decided to implement it the same way I did authors because it felt like a many to many relationship: a webpage can have many categories, a category can be applied to many webpages. Is it wrong?
Also, I like the idea of changing webpages to contentitems.
In regards to classes, I do know how to make classes, initialize objects, etc. But that's it, I only know how to do it. I don't know the different design patterns, or whether I'm doing it correctly or just implementing ruining the need for OOP.
Code Complete will be a very worthy purchase.
Also, this is in reply to your recent post in your CSS topic in Coffeehouse. I didn't want to post it there since that topic is about CSS, and not presentation transformation.
I was thinking of providing support for different presentation forms in my design as well: HTML, PDF, XML(okay this isn't really presentation but this is just for raw data output), XAML (when it's finalized, I have excellent plans for this ), MS Word, MS Excel, VML (or whatever that XML language is for text-to-speech), etc. The way I'm considering doing these transformations is by the use of PML (Presentation Markup Language) Templates. These templates define the presentation structure of elements. With the use of these PML templates, I can just define a template and then my Presentation class libraries will take that template and produce whatever format I decide.
Kind of cool if you think about it. It's still a concept though.
MVP: your category arrangement sounds fine. What I'm suggesting is you think about what exactly you mean by "category". Write out some scenarios. Think about all of the different ways pages can be categorized, and how you may want to use these categories as you develop websites with your system. Depending on your needs, and in the interests of simplifying your SQL query logic, you may decide you want to describe these categories more exactly.
If this doesn't make any sense, your needs will become more clear three or six months into your project.
kenfine wrote:MVP: your category arrangement sounds fine. What I'm suggesting is you think about what exactly you mean by "category". Write out some scenarios. Think about all of the different ways pages can be categorized, and how you may want to use these categories as you develop websites with your system. Depending on your needs, and in the interests of simplifying your SQL query logic, you may decide you want to describe these categories more exactly.
If this doesn't make any sense, your needs will become more clear three or six months into your project.
The categories will only be used to define stuff like reference, how-to, gallery, etc.. I'm thinking towards more of a "related links" approach.
How does my PML idea sound?
Also, I updated my diagram...with TONS of Tables (~25)
Here it is (in link version this time):
The clientstats and webstats were a last thought. Don't ask.
Have ya'll checked out the DNN (DotNetNuke) system? I wrote my own CMS based off of the IBUYSpy portal and ran it for for the last couple of years until I finaly decided to stop being stubern about the DNN system being written in VB instead of C#. With the amount of poeple working on this open source project it has turned out to be be quite impressive.
If nothing else it is a very good reference guide to what you can do with SQL, .Net, and XML. The skinns allow for a very versital layout and adding updates are a snap.
Whether your looking at a solutoin that will fit your project or just looking for a reference this is a must have project to go over. Not to mention you will find a lot of help from the developer comunity on it.