Combining Tables Into Master Table Design Problem

[raphael232]

Hi, for a long time now i've been creating a different table for each section on my site (eg one for news and one for events etc). However i feel this adds alot of repeated work. I'm currently in the prcoess of combining them into a documents table. Here's what i have come up with so far:

Documents:
- ID (primary key)
- SectionID
- Title
... (other shared fields)

Sections:
- ID (primary key)
- SectionName

The problem i'm having is dealing with the extra fields that are unique to specific sections. My first thought was to create an additional table for each section with the extra fields, but this kinda is going back to what i started with. Here's the additional tables i come up with to combat this problem:

Attributes:
- ID (primary key)
- SectionID (section the attribute belongs too)
- FieldName

DocumentAttributes:
- ID (primary key)
- DocumentID (document attribute for)
- FieldID
- FieldValue

Which i thought was great at first but when i tried to implement and do a query to get the news i put:

SELECT dbo.Documents.* FROM dbo.Documents INNER JOIN DocumentFields ON Documents.ID = DocumentFields.DocumentID WHERE SectionID = 1

I expect you may have noticed the problem, the Documents table contains a single row for a record but the DocumenFields table contain multiple rows.

My next solution is to change my attributes table to:

- ID
- DocumentID
- AdditionalAttribute1
- AdditionalAttribute2
- AdditionalAttribute3
...

And join the document to the DocumentID and then i have my attributes. Now i need to create a new field for each attribute i wish to use. I'm not quite sure how effective this is because it would contain alot of redundant fields across all my sections of the site.

I'd appreciate peoples feedback to my problem and any solutions you may have as i'm sure i'm not the only person in the world who's encountered this problem before.

Thanks

 

[philhege]

With a one-to-many relationship, such as the one between Documents and DocumentAttributes, multiple-row results are natural and expected. In and of itself, that's not a problem.

The problem comes with having to adjust your display code to handle the output.

An alternative is to use some pivot-table code to turn the results over. This has its problems when your documents have different numbers of attributes, so you'd have to accommodate fields up to the maximum used by any one document.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Gooser]

In my humble opinion, Normalize.

Third normal form works for most instances, sometimes you have to overnormalize, sometimes de-normalize for performance or some other reason. But, a normal database is best for the maintenance v. performance tradeoff.


v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[ralphiooo]

Hi, thanks for replaying. I had a try at normalizing but it's been along time since i last did it. I usually take it for granted these days. However i can't see how it's going to help with my problem since my initial database scheme seems well normalized. It's a case of making it simpler so i don't have to create loads of table adapters for my dataset.

I've been thinking this one over for the last few hours and as far as i've got is to chuck all attributes in the documents table and for the sections i don't need the attributes i set them to null. However this seems a pretty shoddy solution and it's simpler to stick with what i have.

Appreciate further feedback.

Cheers

 

[Gooser]

You have to brainstorm and decide what is your main unit-of-work (UOW)... Is it a document?

Once you've decided what your UOW is, you need to define what its attributes are.

Anyways...what I am trying to get at here is design; design, Design, DESIGN! Every hour spent coming up with a good design at the start will save 10 hours down the road. (estimate/opinion based on experience, not documented fact)

I would steer clear of having any data that describes what the record in a column holds...if that makes any sense.

More tables DOES NOT EQUAL harder to maintain. It is through finding which tables [and attributes] you need and which tables [and attributes] can be removed that you will find the path to the perfect database. Only when you arrive in the garden of normalization will your maintenance time diminish and your development time be set free to create the balanced designs that your mind's deep pools hold. Wander not down the path of easier for what is easier now will most certainly become more difficult as time goes on, and what will be easier down the road will almost certainly require more effort to develop in the short term. Rather, imagine balance--focus on what is optimum. Imagine the perfect world, then model it and build it. Go forth, grasshopper, develop applications that are like the panda bear--warm and fuzzy, and not like the bamboo-shoot--tall, narrow-minded, and hard to rid from your garden.



Sorry for the "Mr. Miagi" voice here, but sometimes when my mind wanders, I have to follow.




v/r

Gooser

Why do today
that which may not need to be done tomorrow --me

 

[philhege]

I still think that this is a UI layer problem.

Your two tables

Attributes:
- ID (primary key)
- SectionID (section the attribute belongs too)
- FieldName

DocumentAttributes:
- ID (primary key)
- DocumentID (document attribute for)
- FieldID
- FieldValue

exhibit correct design (with maybe some field name tweaking). You just have to deal with the natural consequences of a one-to-many query.

Or, like you said, chuck it all.



Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[raphael232]

Hi, i think the one to many relationship isn't going to work. I don't want to deal with too much additional code. Now i've come up with the following:

Documents
- ID
- Title
- Content

News
- DocumentID
- SectionID
- ExtraAttribute1
- ExtraAttribute2

Events
- DocumentID
- ExtraAttribute3
- ExtraAttribute4

Which is like my first attempt. However to make this work i wish to create a query that does a case statement ie

SELECT Document.Title, Document.Content FROM Documents LEFT OUTER JOIN (SELECT CASE Document.SectionID = 1 THEN News CASE Document.SectionID = 2 THEN Events)

I have no idea if this is even possible or will work. If it is i'd appreciate if someone could give me the correct syntax, if not appreciate if someone could tell me now before i get to far into this.

Thanks

 

[Gooser]

raphael-

--yours
SELECT Document.Title, Document.Content FROM Documents LEFT OUTER JOIN (SELECT CASE Document.SectionID = 1 THEN News CASE Document.SectionID = 2 THEN Events)

There's no ON clause in yours. When you JOIN, it has to be ON some condition. So it won't work. That, and Document. has no SectionID.

--try something like this (?)

SELECT Docs.Title
     , Docs.Content
     , News.SectionID
     , News.ExtraAttribute1
     , News.ExtraAttribute2
     , Even.ExtraAttribute3
     , Even.ExtraAttribute4
   FROM Documents AS Docs
   JOIN News
      ON Docs.ID = News.DocumentID
   JOIN Events AS Even
      ON Docs.ID = Even.DocumentID

You'll have to handle the empty columns still, but maybe someone else can offer you a better way.


v/r

Gooser

Why do today
that which may not need to be done tomorrow --me