Best practice normalization database?

[tfit]

I spend to much time figuring out how to do my normalization. It needs to get implemented soon. :) In fact I'm too late already. I have to make a site which has daily items and subitems accessed through the main page. However not all of the subitems will be update daily. I have tree possible solutions, but I have alot of repeating data or empty cells. Can someone offer me to a better solution?


Solution 1: problem empty cells


---------------------------------------------------------
|date |home | rites | maidens| tedeum | triumph |
---------------------------------------------------------
|20080402 |xxbx | | cccx | | xxxyxx |
|20080403 |xxrx | | xxcx | bbbbbx | |
|20080404 |xxxx | bbbxx | cccxx | bbbx | |
|20080405 |xxcxx | xxxyxx | xxxcx | | xxxxy |
---------------------------------------------------------


solution 2: problem repeating data, many tables note: just 3 tables listed


----------------------
|date | home |
----------------------
|20080402 | xxbx|
|20080403 | xxrx|
|20080404 | xxxx|
|20080405 | xxcx|
----------------------

----------------------
|date | rites |
-----------------------
|20080404 | bbbxx |
|20080405 | xxxyxx|
----------------------

-------------------------
|date | maidens |
-------------------------
|20080402 | cccx |
|20080403 | xxcx |
|20080404 | cccxx |
|20080405 | xxxcx |
-------------------------




solution 3: problem again repeating data
------------------
|id| name |
------------------
|1 | home |
|2 | rites |
|3 | maidens |
|4 | tedeum |
|5 | triumph |
|----------------|

---------------------------------
|id| date | title|
---------------------------------
|1 | 20080402| xxbx |
|3 | 20080402| cccx |
|5 | 20080402| xxxyxx|
|1 | 20080403| xxrx |
|3 | 20080403| xxcx |
|4 | 20080403| bbbbbx|
|1 | 20080404| xxxx |
|2 | 20080404| bbbxx |
|3 | 20080404| cccxx |
|4 | 20080404| bbbx |
|1 | 20080405| xxcxx |
---------------------------------

sorry about the wiggling :mad:

[boogyman]

telling us the foreign keys between the tables would be helpful for future reference, however generally if you have a table that has alot of repeating string data, it is possible that you might want to create a new table and use a unique identifier and have a field for the unique string

for instance... if you have a list of users, and you have multiple users at 1 company, it is possible that you should have a separate companies table and use the "company_id" in the user table.

if you are worried about having a repeating date, do not worry about that. it's really not that big of a deal

[ghostsquad]

I agree. It's not that big of a deal until you get 100's or 1000's of records. Getting to know 2nd normalization is a good idea though, 3rd normalization is almost of waste of time, and can actually make things more complicated and decrease performance.

check this out:
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[tfit]

Thanks,
So can I interpreter this that repeating values is better than empty cells?

[boogyman]

Thanks,
So can I interpreter this that repeating values is better than empty cells?

yes and no... it depends on the data.
date cells repeating = okay
string cells you wish to index (search) = not okay for either
article titles repeating = okay
article content repeating = better empty

if you give us a general overview of your system we can let you know what would be worth normalizing and what would be okay to have a (possible) empty cell

[tfit]

I've come up with this and I think it's a practical design


stories style pre_proc header
--------------------------------------------
cat_id style_id prep_id cat_id
title style_dt prep_dt category
text
style_id
prep_id
date
allow

And three rows of stories database will look something like this

1 - news of today - some text - 1 - 3 - 20080408 - y
3 - eeepc rocks - sme other text - 2 -3 - 20080408 - y
1 - news of tomorrow - text -1 -3 -20080409 - n