database design

[redapple]

Hi,
I was assigned to develop a site which is basically comprised of numerous articles. Each articles is very long and has multi-sections, and each subsection has its own subsection... so we decided to split each articles into small parts to accomdate easy reading and maintenence.

So some of the subsections will have title and body, and some of them will only have a title followed by its subsections. The tree-mode can represent the structure of each article very well:
*
/\ / \ * a *
/ \ / * a * *
* --- sections have subsections
a --- sections have only titles, but no contents

I tried using 2 tables, one for "*"s, the one for the "a"s. But I don't think it worked well. Because each subsection needs to find its parent, and it turned out to be looking for its parents in 2 tables, which I think is inefficient. But I didn't come up with any other solutions yet

Can somebody help? Does anyone have any similar experience with these kind of situation before?

Thank You

Here are the fields will be needed to build the tables:
* ArticleId
* ArticleTitle
* ArticleBody
* ArticleParentId
* ArticleOrderId

 

[raj1922]

* Treat this as Table A
/\ / \ * a * Treat this as Table B
/ \ / * a * * Treat this as Table C

Table A will have A_ID(Identity) as Primary Key & Fields
Table B will have A_ID as Foreign Key, B_ID(Identity) as Primary Key & Fields
Table C will have A_ID as Foreign Key, B_ID as Foreign Key, C_ID as Primary Key & Fields

so when you join you join on

select *
from A,
B LEFT OUTER JOIN C ON
(B.B_ID = C.B_ID)
where A.A_ID = B.A_ID

Hopefully this helps.

 

[kishleo]

i am not sure what you want to do actually, can you explain it more clearly.....but as for my understand of your question

i think there is no need for many tables, you can create only one table and add two extra columns TitleID and PID


suppose
a
/\ / \ b c d
/ \ / e f g h


here for a the TitleID is id 1 and the PID is null
for b TitleID is 2 and the PID is 1
for c TitleID is 3 and the PID is 1
for d TitleID is 4 and the PID is 1
for e TitleID is 5 and the PID is 2
for f TitleID is 6 and the PID is 2
for g TitleID is 7 and the PID is 4
for h TitleID is 8 and the PID is 4

usind these values we can easily track the things
see here c,e,f,gand h dont have any contents so 3,5,6,7, and 8 is not a PID for any Title
ô¿ô Kishore