I have 4 tables
hubs | countries | categories | news
here hubs and countries have many to many relation
country_hub
id
hub_id
country_id
and then this pivot table country_hub has many to many relation with categories so I did like
category_country_hub
id
country_hub_id
category_id
and again this table has many to many relation with news table
category_country_hub_news
category_country_hub_id
news_id
this is giving me a complicate relation to query
so I am thinking of modifying the relation like
country_hub
country_id
hub_id
category_country_hub
country_id
hub_id
category_id
category_country_hub_news
hub_id
country_id
category_id
news_id
which is one to many relation with hubs/countries/ categories
is there any better way to handle these kind of relation please help or any tutorials links
Ok, clear now. Expanded comments below, here is the summary:
tl;dr:
1) your revised approach makes more sense to me.
2) your naming conventions could use some polish, will improve readability for humans (specifically the 'tokens' in table names matching order of columns in table, fwiw the database itself won't care).
3) book: I will recommend "SQL for Smarties" (Celko), which goes into some of the modeling issues you're dealing with. http://www.amazon.com/Joe-Celkos-Smarties-Fourth-Edition/dp/0123820227
Let's dig into the table definitions... I can't reason well from a text summary, my brain works better if I can see examples.
Let me know if the examples are (more or less) suitable.
Seems ok to call these fact tables.
|-----------------|---------------|---------------|--------------------|
| select * from | select * from | select * from | select * from |
| COUNTRIES | HUBS | CATEGORIES | NEWS |
|-----------------|---------------|---------------|--------------------|
| id : name | id : name | id : name | id : title |
| --- : --------- | --- : ----- | --- : ------- | ---- : -----------|
| 101 : China | 201 : X | 301 : Red | 401 : 'aa aaaa a'|
| 102 : Nepal | 202 : Y | 302 : Blue | 402 : 'bbbb b bb'|
| 103 : Australia | 203 : Z | 303 : Green | 403 : 'cc ccc cc'|
| 104 : NewZealand| ...etc... | 304 : Orange | 404 : 'ddddd d' |
| ...etc... | | ...etc... | 405 : 'ee eeee' |
|-----------------|---------------|---------------|--------------------|
Observation: These are not really dimension tables, I don't see an obvious hierarchy here.
Let's carry this out a little further.
|-----------------------|---------------------------|--------------------------|
| select * from | select * from | select * from |
| COUNTRY_HUB | CATEGORY_COUNTRY_HUB | CATEGORY_COUNTRY_HUB_NEWS|
|-----------------------|---------------------------|--------------------------|
| : : country| : country : category | cat_cnt : news |
| id : hub_id : _id | id : _hub_id : _id | id : _hub_id : _id |
|---- : ------ : -------|----: -------- : ----------| ---- : -------- : ------ |
| 11 : 101 : 201 | 21 : 11 : 301 | 31 : 21 : 401 |
| 12 : 101 : 202 | 22 : 11 : 303 | 32 : 21 : 403 |
| 13 : 101 : 203 | 23 : 12 : 302 | 33 : 21 : 404 |
| 14 : 102 : 200 | 24 : 12 : 304 | 34 : 22 : 405 |
| ...etc... | ...etc... | ...etc... |
|-----------------------|---------------------------|--------------------------|
Yes, this is starting to look complicated. :-)
observation: If you were going to stay with the approach, I think it could be a little easier if you follow a naming convention embedding the Raw Data tables last:
Original tbl names | Notes
----------------------------|------------------------------------------------------
COUNTRY_HUB | Two raw-data id#'s (hub_id & country_id)s
----------------------------|------------------------------------------------------
CATEGORY_COUNTRY_HUB | One raw data id#, last column (category_id), but CATEGORY_... first
| token in the table name.
| I will suggest COUNTRY_HUB_CATEGORY would be easier to read
| for human readers, since both right-most column and right-most token
| in the table name tie back to the same concept (the CATEGORY raw data table).
----------------------------|------------------------------------------------------
CATEGORY_COUNTRY_HUB_NEWS | One raw data id#, last column (news_id), also _NEWS is last token
| in the table name, easier for human readers to parse & follow.
----------------------------|------------------------------------------------------
This looks better.
|-----------------------|-------------------------------|-------------------------------------------|
| select * from | select * from | select * from |
| COUNTRY_HUB | CATEGORY_COUNTRY_HUB | CATEGORY_COUNTRY_HUB_NEWS |
|-----------------------|-------------------------------|-------------------------------------------|
| : country: hub | : country : hub : category| : hub : country : category : news |
| id : _id : _id | id : _id : _id : _id | id : _id : _id : _id : _id |
|---- : ------ : -------|----: --------: ---- : --------| ---- : ----- : ------- : -------- : ----- |
| 11 : 201 : 101 | 21 : 201 : 101 : 301 | 31 : 101 : 201 : 301 : 401 |
| 12 : 202 : 101 | 22 : 201 : 101 : 302 | 32 : 101 : 201 : 301 : 401 |
| 13 : 203 : 102 | 23 : 201 : 101 : 303 | 33 : 102 : 201 : 301 : 401 |
| 14 : 204 : 102 | 24 : 201 : 102 : 301 | 34 : 102 : 201 : 301 : 402 |
| ...etc... | ...etc... | ...etc... |
|-----------------------|-------------------------------|-------------------------------------------|
About Naming Conventions The table-name "tokens" still don't follow the column order. As a favor to yourself and future maintainers, consider changing that:
COUNTRY_HUB is fine.
CATEGORY_COUNTRY_HUB still seems flipped, use COUNTRY_HUB_CATEGORY
CATEGORY_COUNTRY_HUB_NEWS doesn't follow from previous, I would use COUNTRY_HUB_CATEGORY_NEWS and adjust the columns accordingly (though I
don't know enough about your data relationships to comment on what is
the best order).
The thing that you have implicit in your naming is a rough "category"
overly simplistic:
Each COUNTRY has 0..many HUBS.
Each HUB has 0..many CATEGORIES.
Each CATEGORY has 0..many NEWS items.
I'll suggest you work on making your table-name "tokens" match the "column order". You seem to have (in order of few to many):
COUNTRY : COUNTRIES (relatively few)
HUB : HUBS (# of HUBS greater than # of COUNTRIES)
CATEGORY : Assigned CATEGORIES (# of COUNTRY+HUB+CATEGORY combinations exceeds # of previous)
NEWS : Assigned NEWS items (# of COUNTRY+HUB+CATEGORY+ combinations exceeds # of previous)
Let's do a little data modeling and describe the relationships...
COUNTRY <*----*> HUB
Each COUNTRY has 0..many HUBS.
A given HUB may be associated w/multiple COUNTRIES.
HUB ----*> CATEGORY
or..?
COUNTRY + HUB <*----*> CATEGORY
Your tables suggest CATEGORIES do not simply associate directly with a given HUB.
Consider HUB.id=101 name='X'
X.China.categories = ( Blue, Yellow );
X.Nepal.categories = ( Orange, Green );
X.Australlia.categories = ( ); e.g. none.
Instead of all countries associated with that HUB sharing the same "HUB CATEGORIES",
it sounds like the CATEGORIES are like "tags" and that the various countries involved
with a given HUB can have their collection of 0..many CATEGORIES.
It seems weird, but I don't know your data.
In the interests of simplifying I would try to make CATEGORIES be HUB-specific, not
HUB+COUNTRY specific... but that may be unavoidable for you.
COUNTY + HUB + CATEGORY <*----*> NEWS
This suggests that a given NEWS item can be associated with 2+ (COUNTRY+HUB+CATEGORY) triples.
If that is what you need, then it can't be avoided.
You're going to have a challenge keeping all of the relationships up to date.
You will want to study up on foreign key constraints and cascading deletes.
I did greatly enjoy this book: SQL for Smarties (Celko), which goes into some of the modeling issues you're dealing with.
Splitting them out the way you are has the advantage of avoiding some anomalies (one of the examples Celko uses involved class scheduling at a school: teachers, classes, rooms, students and the relationships between them). I will recommend the book, I think it reads well.