[Table assignment] Which design is better ?
Hello:
If a table X contains several columns, and might contain many (millions) rows, all rows can be splited into 5 groups by value of one column.
then, below two design
1. All rows in one table, and use a column to differentiate the groups
2. Split rows into 5 tables, without differentiate column
Which design will get better performance for query in most DB server?
My queries need to join all 5 groups by other columns, like :
sel .....
from Z z
join X x1 on x1.grp=0 and x1.id=z.id and ...
join X x2 on x2.grp=1 and x2.id=z.id and ...
join X x3 on x3.grp=2 and x3.id=z.id and ...
join X x4 on x4.grp=3 and x4.id=z.id and ...
join X x5 on x5.grp=4 and x5.id=z.id and ...
in case 1
or
sel ...
from Z z
join X1 x1 on x1.id=z.id and ...
join X2 x2 on x2.id=z.id and ...
join X3 x3 on x3.id=z.id and ...
join X4 x4 on x4.id=z.id and ...
join X5 x5 on x5.id=z.id and ...
in case 2
[1019 byte] By [
jesvh] at [2007-11-20 11:43:46]
# 1 Re: [Table assignment] Which design is better ?
Hi all.
If you split row into 5 table you'll get 5 one-to-one relationships between the main table and the detail tables. One-to-one relationships are rarely used, because they usually arouse performance problems especially when tables are so big and when the details tables aren't mutually exclusive each other.
So, in my opinion the better choice is one table with the special column that defines the group of fields. If you choose this solution you won't need to perform join in SELECT statement, because all columns belong to the same table.
# 2 Re: [Table assignment] Which design is better ?
Thanks for reply
But I can't get your meaning below :
If you choose this solution you won't need to perform join in SELECT
statement, because all columns belong to the same table
I think my SELECT statement has to include fields of z, and x1, x2, ... ,
although in fact x1, x2, ... are the same table.
jesvh at 2007-11-9 13:46:41 >
# 3 Re: [Table assignment] Which design is better ?
Well
Maybe I've failed understanding the structure of your table; I thought to a table where some fields make sense for some type of records, so the dubt is having only one table with all columns or many table where all of them own the fields belonging to a particulary group (I hope I've explained cleary).
But probably all columns are "valid" for each group, so I imagine something like this.
Table VEHICLE
VEHICLE_ID -- Key
VEHICLE_TYPE -- C for car, V for vans, A for airplane
MODEL
COLOUR
SPEED
PRICE
In the example above the table VEHICLE contains information about cars, vans and airplanes (the groups of records) and VEHICLE_TYPE is the special colUmn that differentiate the groups.
All record are contained in VEHICLE, so you don't need any join. For example, if you want find all red cars you'll write
SELECT *
FROM VEHICLE
WHERE VEHICLE_TYPE = 'C'
AND COLOUR = 'RED'
This is the solution with only one table, and of course if the table is big will be performance problems, so you would like to have three tables (CARS, VANS and AIRPLANES) with the same structure (except for VEHICLE_TYPE field that is unnecessary).
If so, probably the best choice depends on the kind of queries you are going to perform: if you search always using key there won't be performance problems using one table, but if your queries work on the type maybe having three tables is better.
I hope this will help you.
# 4 Re: [Table assignment] Which design is better ?
Sorry, maybe I didn't descripted it well.
Main table is Z
the detail table X is the table we discuss about, linclude fields :
id
grp ( if one table )
name
....
....
if only one detail table, my query will look like
SELECT z.id, x1.name, x2.name, x3.name, x4.name, x5.name, ...
FROM Z z
join X x1 on x1.id=z.id and x1.grp=0
join X x2 on x2.id=z.id and x2.grp=1
join X x3 on x3.id=z.id and x3.grp=2
join X x4 on x4.id=z.id and x4.grp=3
join X x5 on x5.id=z.id and x5.grp=4
if split detail to multi- table, it look like :
SELECT z.id, x1.name, x2.name, x3.name, x4.name, x5.name, ...
FROM Z z
join X1 x1 on x1.id=z.id
join X2 x2 on x2.id=z.id
join X3 x3 on x3.id=z.id
join X4 x4 on x4.id=z.id
join X5 x5 on x5.id=z.id
All JOIN are necessary.
Which one will get better performance ?
jesvh at 2007-11-9 13:48:46 >
# 5 Re: [Table assignment] Which design is better ?
OK, now it's clear.
The question about the best performance is very interesting (and hard also).
In both cases you have to perform the joins with the same number of tables, but with the first solution (one table with group field) you join n time the same huge table X, and you won't use the full index, because the conditions are on the key ID and the special group field, whereas with the second solution you join n small(est), different tables and yon can use full index; so in my opinion - I repeat, in my opinion - probably the second solution is better than the first one.
Of course, we have to consider others things; first what db you're using, then if you must solve your requirement using only one query: if you can develop a stored procedure or something like this, with the first solution you should join Z and X once, exctract the group filed and work on it.
I hope I'm giving you some proper ideas...
