May 6, 2008 at 9:43 pm
i've have these following table
tblTeamDetails
Department | Team_Name | Team_ID | Status
---------------------------------------------------
Computer | Spider-Man | 0002 | Available
Computer | Hulk | 0003 | Available
Computer | Iron Man | 0004 | Available
Computer | Wonder Women | 0005 | Available
Sales | Robocop | 0006 | Available
Sales | Hancock | 0007 | Available
Sales | Superman | 0008 | Available
Sales | Flash | 0009 | Available
Sales | Storm | 0010 | Available
Account | Punisher | 0011 | Available
Account | She-Hulk | 0012 | Available
Account | He-Man | 0013 | Available
Account | Wolverine | 0014 | Available
Corporate | Thor | 0015 | Available
Corporate | Capt. America| 0016 | Available
*Combination of Department and Team_ID generate a primary key.
Is that the best design? Can anyone show me the best design?
May 6, 2008 at 10:22 pm
You can divide the table into two parts. Have a Dept table with Dept Id and Dept Name. In Team Details have Dept ID, Team ID, Team Name and status.
There is another solutions:
Department_Details: Dept_ID and Dept_Name
Team_Details: Team ID, Team Name and Status
Dept_Team_Details: Dept_ID, Team_ID
🙂
Other may have better solutions.....:)
May 7, 2008 at 1:20 am
If divide table into more table, is that affect the performance? It is because if we're running the queries, it will cost more join table. More inner join we're using, it will take more memory.
Correct me if i'm wrong. Plz advice.
May 7, 2008 at 1:48 am
Integrity is more important than shaving a few milliseconds off your processing time. With your current design, what's to stop someone inserting a row such as this?
Corporate | Capt. America| 0014 | Available
Look up normalisation, and make sure you understand the principles. If you are designing an OLTP database, then take the advice of the other users above and split the tables as they suggest.
John
May 7, 2008 at 4:52 am
Kindest Mr. Anirban Paul and Mr. John Mitchell ,
Tq to both of you. Really appreciate it.
May 7, 2008 at 4:57 am
Yoiu are welcome. But do not forget the John's advice. Remember while designing tables.
🙂
May 7, 2008 at 3:28 pm
Here's a question. Would overall performance be better when joining on a dept id vs name because of the comparing of int vs string? I would assuming other tables store info about "dept" and having a ton of tables listing "Computer" vs 1 would save space/speed and memory especially with indexes.
please correct me if I'm wrong or if it doesn't make much of a diff anyway.
May 7, 2008 at 5:51 pm
bcronce (5/7/2008)
Here's a question. Would overall performance be better when joining on a dept id vs name because of the comparing of int vs string? I would assuming other tables store info about "dept" and having a ton of tables listing "Computer" vs 1 would save space/speed and memory especially with indexes.please correct me if I'm wrong or if it doesn't make much of a difference anyway.
Unless the strings are huge, the difference is usually minuscule. The presence or absence of appropiate indexes and their selectivity makes a much bigger difference.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 8, 2008 at 1:24 am
bcronce (5/7/2008)
Here's a question. Would overall performance be better when joining on a dept id vs name because of the comparing of int vs string? I would assuming other tables store info about "dept" and having a ton of tables listing "Computer" vs 1 would save space/speed and memory especially with indexes.please correct me if I'm wrong or if it doesn't make much of a diff anyway.
Again, this is as much a question of integrity as it is of performance. In a normalised database, each piece of information should be stored only once. Therefore, if you have an ID column in your department table, you don't need to repeat the department name in any child table - you just use the ID. There is a lot of debate about whether to use natural or surrogate keys, so you need to read the discussion on that (search this site, or wider) and make your own decision. After that, the decision on what to join on will be made for you.
Hope that makes sense
John
May 8, 2008 at 7:16 am
rbarryyoung (5/7/2008)
Unless the strings are huge, the difference is usually minuscule. The presence or absence of appropiate indexes and their selectivity makes a much bigger difference.
It's nice to know that a small string comparision is low compared to other overhead/etc. But wouldn't using an int still help reduce index bloating?
Sorry if these questions are dull, but I like to know how everything is treated in the background 🙂
May 8, 2008 at 10:52 am
bcronce (5/8/2008)
rbarryyoung (5/7/2008)
Unless the strings are huge, the difference is usually minuscule. The presence or absence of appropiate indexes and their selectivity makes a much bigger difference.It's nice to know that a small string comparision is low compared to other overhead/etc. But wouldn't using an int still help reduce index bloating?
Sorry if these questions are dull, but I like to know how everything is treated in the background 🙂
Smaller index columns do mean smaller indexes, yes. But that was not the question originally asked. Performance is not normally affected much unless the index-columns length are fairly large, or the total table-columns length is really small.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 9, 2008 at 7:07 am
From the sample data, it appears that department and team have a one to many relationship: one department can have many teams but each team has exactly one department. If that is true, wouldn't you want to have:
Department_Details: Dept_ID and Dept_Name, (other attributes...)
Team_Details: Team ID, Team_Name, Dept_Id and Status (other attritures...)
You would only need a single join and the table structure itself would enforce the rule of only one department per team.
If a team CAN belong to more than one department (many to many relationship) then the previous design would be required.
May 10, 2008 at 9:56 am
Noticed that you did state "Combination of Department and Team_ID generate a primary key.", a normalized schema is below. Notice the on update cascade for DepartmentName.
You could add a DepartmentId integer column to the Department table and then change the DepartmentTeam table to use the id instead of the name. Past investigation shows that a 4 Byte integer key instead of a 16 byte varchar, gains about 10% in performance for joins.
create table dbo.Department
(DepartmentName varchar(255) not null
, constraint Department_P primary key (DepartmentName )
)
create table dbo.DepartmentTeam
(DepartmentName varchar(255) not null
,TeamId integer not null
,TeamName varchar(255) not null
,TeamStatus varchar(255) not null
, constraint DepartmentTeam_P primary key (DepartmentName,TeamId )
, constraint Department_F_DepartmentTeam foreign key
(DepartmentName) references dbo.Department
on update cascade on delete no action
)
go
SQL = Scarcely Qualifies as a Language
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply