Welcome Guest, Not a member yet? Register   Sign In
Good database practice question
#1

[eluser]september28[/eluser]
Hi there,

I have a quick question.

I have a db table that stores entries of posts. each post has a country assigned to it. e.g one post might have the country code "es". However, it is possible that the post has more than 1 country. It may have 3 or 4 of even about 20. eg a post may belong to the countries: es, gb, us, fr, gr, cn etc...

My question is this: should I just add the codes into the table separated by a comma (e.g. have a field called country that would be varchar and have "es,gb,us,fr,gr,cn" OR should I create a separate table with the fields id, post_id and country_id. I know that the second method would conform to the normal form rules however i expect there to be thousands of entrys in this table and I just wondered if it would be more efficient to do it the first way (avoiding a select statement that has to scan thousands of lines to get all of the country codes for each post)

Does anyone have an opinion??

Cheers,

Dan
#2

[eluser]Dam1an[/eluser]
Depends, if you plan on selecting based on the country code, I would go with a row for each country (with the country code and post id), if you just plan on showing it alongside the post, then the comma list would be fine
#3

[eluser]cwt137[/eluser]
I agree with Dam1an.
#4

[eluser]überfuzz[/eluser]
NO!!!

Make a table with the codes and join in via a index table.

Code:
codes
id | code
-----------------
1  | swe
2  | fin
3  | eng

Code:
post
ID | text  | time        | etc
---------------------------------
1  | lorem | (timestamp) | ...
2  | ipsum | (timestamp) | ...
3  | lorem | (timestamp) | ...
4  | ipsum | (timestamp) | ...

Code:
post_codes
id  | code | post
-------------------
1   | 1    | 4
1   | 2    | 4

This example would give the post with ID 4 the codes swe and fin. Better to keep the database tidy!
#5

[eluser]Jagar[/eluser]
When I encounter something like that, which I am now,

I create a linking table that way is many to many

SO let's say you have a table called
[Country]
Country_id
country_name
etc.

then another one
[post]
post_id
post_title
etc

then linking
[country_post_link]
FK_post_id
FK_country_id


Note: FK = foreign key.

I hope that gives you some idea
#6

[eluser]cwt137[/eluser]
[quote author="überfuzz" date="1241050177"]NO!!!

Make a table with the codes and join in via a index table.

Code:
codes
id | code
-----------------
1  | swe
2  | fin
3  | eng

Code:
post
ID | text  | time        | etc
---------------------------------
1  | lorem | (timestamp) | ...
2  | ipsum | (timestamp) | ...
3  | lorem | (timestamp) | ...
4  | ipsum | (timestamp) | ...

Code:
post_codes
id  | code | post
-------------------
1   | 1    | 4
1   | 2    | 4

This example would give the post with ID 4 the codes swe and fin. Better to keep the database tidy![/quote]

That's what I was thinking when the original poster said a row for each country. Just remember that with this approach, if you are going to have 1000's of rows in the post_codes table, it probably is going to be a good idea to index the code field. Specially if you are doing queries based on country.
#7

[eluser]Jonas G[/eluser]
i agree with überfuzz...




Theme © iAndrew 2016 - Forum software by © MyBB