Opinions please ...

[codestorm]

OK, here's the scenario:

Our site currently has (or is proposed to have) DIFFERENT database tables to store:
a) the (HTML and text) content that are displayed on pages,
b) the hyperlinks that are displayed on pages, and
c) other items (e.g. XML docs, XSL docs, PDF docs, HTML docs, images, ASP code to be dynamically executed, etc)

I thought that in general it would be cleaner to store all of these in the one table (kind of a global document/object table). However, my boss is worried that this will create excessive load on the table, disk hotspots, etc.

I'm looking for some good arguments for doing it my way.
What are your opinions?
codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[jlsmithhartfiel]

In my opinion, your boss' layout is correct. If you look at it purely from the standpoint of:
each page will have multiple hyperlinks
each page will have multiple other objects

So, how would you propose to store them all in the same table? Each time you want to add a hyperlink, for example, would you need to modify the schema? It would be much easier to just add another record in the hyperlinks table.

My 2 cents... Hope this helps,
Jessica

 

[codestorm]

The current tables are as such :

* A_Content (C_ContentID)

* L_ContentTextBin (CTB_ContentID, CTB_LanguageID, CTB_Description)

* L_PageContent(PC_PageID, PC_ProductID, PC_SkinID, PC_ContentID, PC_PageRefContentID)

* A_Hyperlink (H_HyperlinkID, H_Target, H_URL, H_QueryString, H_ClassID)

* L_HyperlinkTextBin(HTB_HyperlinkID, HTB_LanguageID, HTB_Description)

* L_PageHyperlink(PH_PageID, PH_ProductID, PH_SkinID, PH_HyperlinkID, PH_PageRefHyperlinkID)

My proposed table would be something like:

* A_Object(O_ObjectID, O_LanguageID, O_Name, O_ContentType, O_Bytes, O_Process, O_Data)--with possibly more fields for createdBy d/t/user and last modified d/t/user.

and would replace the A_Content, L_ContentTextBin, A_Hyperlink and L_HyperlinkTextBin tables - the L_PageContent and L_PageHyperlink tables would refer to the A_Object table for their data. To take it one step further, there woiuld be no need anymore to differentiate between L_PageContent and L_PageHyperlink, as they would refer to records in the same table (A_Object).

Converting to this format doesn't overly affect the A_Content data, as CTB_Description is an ntext field anyway, and the A_Hyperlink table is needlessly complex anyway - the people entering the data know how to design an <a> tag.

codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
You're not a complete programmer unless you know how to guess.
I hope I never consider myself an 'expert'.
<insert witticism here>

 

[jlsmithhartfiel]

Now, that makes sense to me. It seems overly complicated to have 6 tables for that. What you describe would be the way I would go.

Considering you would hit the main table, and then the dependent tables only once, really. Because when you get the dependent records into a record set you're done, right? So, really that would be less table hits, than if the objects were broken out & you had to hit for each link or other object.

Good luck! Hope this helps,
Jessica