Hi,
I have a database design problem that I'm finding difficult to resolve. I'm new to database design
and would very much appreciate any help.
I'm trying to as much as is possible conform to the guidelines laid out in
the principles of normalization. I apprreciate that sometimes for peformance
reasons, it may be necessary to denormalize the data which I'm also considering.
What is key though is that my search is very fast.
I'm searching for people in a 'Members' table. The search is based on the member's
search criteria. My initial design for this was the following:
Members Table
-------------
Member_ID Member_Name Location_ID
-------- ---------- ---------
1 Joe 168
2 Jean 164
3 Sarah 169
4 Jack 170
Search Criteria Table
----------------------
Member_ID Location_ID
-------- ----------
1 165
1 168
2 167
3 169
Location Table
--------------
Loc_ID Loc_LocationName Loc_ParentID
------ --------------- ------------
164 France 0
165 USA 0
166 UK 0
167 Texas 165
168 London 166
169 Dallas 167
170 Delta 167
So, Joe wants to find someone from the USA and London UK; Jean wants
someone from Texas and Sarah wants someone from Dallas.
I've designed the Location table like this because different countries will have different levels
of depth, and I didn't want to have to add a new table for every level of
depth that I may later decide to add.
Jean and Sarah's search criterion are simple (a single inner join I think) but Joe who wants to find
all those in the USA becomes not only more difficult, but more importantly, more time consuming
in terms of performance requiring two or three joins.
I believe that currently, because there is no way of telling how deep a country goes in terms
of location, I would have to assume that that location that I'm searching for could have up to three
levels (if that's my limit). This of course is very time consuming, especially if I'm only searching
for France which only has a single level (in this table anyway).
So I changed the location table to this:
Location Table
--------------
Loc_ID Loc_LocationName Loc_ParentID Loc_Gen
------ --------------- ------------ -------
164 France 0 0
165 USA 0 0
166 UK 0 0
167 Texas 165 1
168 London 166 1
169 Dallas 167 2
170 Delta 167 2
I shan't bore you with where I went on from here but you can probably appreciate that simply finding
all those who are in the USA has become a far greater process that perhaps it should be.
Finally, I considered denormalizing the members table to this:
Members Table
-------------
Member_ID Member_Name Country Location_ID SubLocation_ID
-------- ---------- ------- --------- -------------
1 Joe 166 168 null
2 Jean 164 null null
3 Sarah 165 167 169
4 Jack 165 167 170
But then if I have a single location to search for in my Search Criteria table, I don't know
which field in my Members table to join on without again, doing lots of processing.
Many thanks for reading all the above, I know it's a lot, and I hope it made sense.
Marcus
I have a database design problem that I'm finding difficult to resolve. I'm new to database design
and would very much appreciate any help.
I'm trying to as much as is possible conform to the guidelines laid out in
the principles of normalization. I apprreciate that sometimes for peformance
reasons, it may be necessary to denormalize the data which I'm also considering.
What is key though is that my search is very fast.
I'm searching for people in a 'Members' table. The search is based on the member's
search criteria. My initial design for this was the following:
Members Table
-------------
Member_ID Member_Name Location_ID
-------- ---------- ---------
1 Joe 168
2 Jean 164
3 Sarah 169
4 Jack 170
Search Criteria Table
----------------------
Member_ID Location_ID
-------- ----------
1 165
1 168
2 167
3 169
Location Table
--------------
Loc_ID Loc_LocationName Loc_ParentID
------ --------------- ------------
164 France 0
165 USA 0
166 UK 0
167 Texas 165
168 London 166
169 Dallas 167
170 Delta 167
So, Joe wants to find someone from the USA and London UK; Jean wants
someone from Texas and Sarah wants someone from Dallas.
I've designed the Location table like this because different countries will have different levels
of depth, and I didn't want to have to add a new table for every level of
depth that I may later decide to add.
Jean and Sarah's search criterion are simple (a single inner join I think) but Joe who wants to find
all those in the USA becomes not only more difficult, but more importantly, more time consuming
in terms of performance requiring two or three joins.
I believe that currently, because there is no way of telling how deep a country goes in terms
of location, I would have to assume that that location that I'm searching for could have up to three
levels (if that's my limit). This of course is very time consuming, especially if I'm only searching
for France which only has a single level (in this table anyway).
So I changed the location table to this:
Location Table
--------------
Loc_ID Loc_LocationName Loc_ParentID Loc_Gen
------ --------------- ------------ -------
164 France 0 0
165 USA 0 0
166 UK 0 0
167 Texas 165 1
168 London 166 1
169 Dallas 167 2
170 Delta 167 2
I shan't bore you with where I went on from here but you can probably appreciate that simply finding
all those who are in the USA has become a far greater process that perhaps it should be.
Finally, I considered denormalizing the members table to this:
Members Table
-------------
Member_ID Member_Name Country Location_ID SubLocation_ID
-------- ---------- ------- --------- -------------
1 Joe 166 168 null
2 Jean 164 null null
3 Sarah 165 167 169
4 Jack 165 167 170
But then if I have a single location to search for in my Search Criteria table, I don't know
which field in my Members table to join on without again, doing lots of processing.
Many thanks for reading all the above, I know it's a lot, and I hope it made sense.
Marcus