473,320 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

cross table redundant information

bilibytes
128 100+
hi,

I was wondering how to determine whether an information is redundant or not.

if you have in a table "Main", the basic information for a thing:
Expand|Select|Wrap|Line Numbers
  1.  thing_id | name | image_id | city_id 
ok if i want to know the country_name for some thing_id, i will have to make two subqueries.
because i have the location organized this way:
Table cities:
Expand|Select|Wrap|Line Numbers
  1. city_id | country_id | city_name
Table countries:
Expand|Select|Wrap|Line Numbers
  1. country_id | country_name
A)
i will need (to get the country name of a particular thing):
1. the "country_id" which is in Table Cities
2. the "country_name" which is in Table Countries

B)
But i could have organized my tables differently by putting the "country_id" in the main table, in order to get one single subquery:
1."country_name" which is in Table Countries

so what would be the right organization?

C)
notice that i could have put "country_name" directly in Table Cities, by what, i would have reduced my 2 subqueries to 1 single in the firs example:
1."country_name" which is in table cities

D)
or i could have directly put the country_name in Table Main:
0. i wouldn't need to subquery that information.

ok so what will determine whether to make multiple tables?
-> whenever i get to a new information node, should i split the information into another table? like-> the City has 2 branches, then avoid redundant information into the Main table and take City information to another..

Maybe this is not the best example, this one may be clearer:

I have a Table Car:
Expand|Select|Wrap|Line Numbers
  1. car_id | city_id
(a car is compound of multiple parts: engine, wheels, lights...
each of these parts are compound of others: engine->[subpart 1, 2, 3, 4, 5...])
So for each of these car parts, i should have a table, (a car may be able to receive different engines, so i want to be able to track all the ones that suit).
ex: Table engines
Expand|Select|Wrap|Line Numbers
  1. engine_id | engine_name | car_id | sub_part1 | sub_part2 | sub_part3 ...
if i want to get all the engines for a car i make:
Expand|Select|Wrap|Line Numbers
  1. SELECT engine_name
  2. FROM engines
  3. WHERE car_id = 'mycar'
if i want to get all the engines available in a city(supposing all parts of the car are manufactured in the same city the car is in):
Expand|Select|Wrap|Line Numbers
  1. SELECT engine_name
  2. FROM engines
  3. WHERE car_id IN (
  4.                             SELECT car_id
  5.                             FROM cars 
  6.                             WHERE city_id = 'mycity'
  7.                  )
wouldn't this query last "forever"?

it would have been quicker for this query to have a column city_id in the Table Engines to be able to do:
Expand|Select|Wrap|Line Numbers
  1. SELECT engine_name
  2. FROM engines
  3. WHERE city_id = "mycityid"
This is true, if the repeated rows (like 1000 the same city_id) increase the request processing time-> i don't know...

whilst writing all this post, i am getting to the conclusion that the fact that will determine whether a table should include some redundant information or not, will depend on the queries you make to it.

if i use "Engines" Table more often to get all the "engine_names" in a "city", i'd rather include the "city_id" in that table, else, if i use the table more often to get just the "engine_names" for a specific "car_id", i'd rather not include this redundant information.

"more often" is a bit unprecise, i should say:
if
Expand|Select|Wrap|Line Numbers
  1. ((time1) x number_queries1) + ((time2) x number_queries2)
  2. for first table design
  3.  > 
  4. ((time1) x number_queries1) + ((time2) x number_queries2)
  5.  for second table design,
-> use the second

General rule:
Expand|Select|Wrap|Line Numbers
  1. use the table design for which
  2. the cumulated execution time for each query
  3. multiplied by their respective amount of requests
  4.  is the lowest!

That is funny, it's a good thing to write posts it helps thinking!
i normally don't post when i get myself the answer, but it may be useful for others... despite it's length
Nov 11 '08 #1
0 1487

Sign in to post your reply or Sign up for a free account.

Similar topics

7
by: davegb | last post by:
I posted this message earlier, got a reply to google for a previous reply, which I couldn't find, so here I am again. I've run across another situation in Access I just don't know how to handle....
3
by: EJH | last post by:
I have a Database that has three tables. One of the three is just a table that contains three fields and is filled with reference information. One field is 3-Digit(primary key), the next is...
3
by: amanda | last post by:
Hope someone can help me with this - I've been staring at it stupidly for hours now, convinced there must be an easy way to achieve the results I want: I have a very large table recording every...
7
by: Stephen Poley | last post by:
I have the following situation: - a table of employees, keyed on employee-id; - a table of training sessions, keyed on session-id; - a requirement to log who was present at which session, plus...
1
by: Nothing | last post by:
I have a field, Y, in table A that I need to update from table B, matching on comman field X in both tables. Table A has 10K+ records with field X in it (Field X multipul times). Table B has...
69
by: kabradley | last post by:
Alrighty Guys and Gals, I have another question that I hope you all can help me with. I have a report that uses a cross-tab query as its record source. This cross-tab query is getting all of its...
9
by: Neal | last post by:
http://www.brassattackofspringfield.com/gigs.html CSS at http://www.brassattackofspringfield.com/default.css Opera and IE render caption as desired. Firefox does not. Why? And is there a fix?
1
by: sd_eds | last post by:
I have two fields, "Item" and "Information" in the same table. "Information" contains three items from a seperate table created by a lookup. I would like to substring out the first item and place...
42
by: =?ISO-8859-1?Q?Tom=E1s_=D3_h=C9ilidhe?= | last post by:
I'm currently writing a program and I've got in mind to keep it as portable as possible. In particular I want it to run on Linux and Windows, but I'm also keeping an open mind to any machine that...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.