December 1, 2011 at 9:31 am
I assume the goal is to keep each table as small as possible?
Based on the above assumption, i have some fields in a candidate profile table, job title(dates of employment), obviously these are VERY varied.
Should i leave them in the candidate profile table or create a seperate table,
JobTitle Table
CandidateId GUID
Job Title varchar(200)
and reference them based on a GUID, stored in the candidate profile table ?
Thanks
December 1, 2011 at 9:48 am
It depends.
Normalization is one of those things you want to have in a RDMS but can be its design can overkill if you don't anticipate or you know for certain there will be no significant growth over time. I personally attempt to normalize my data as much as possible from the get-go, so I don't need to do any crazy work later when I realize my DB/table growth has grown exponentially....but that's just me.
There's loads of good articles on google about normalization practices. I'm be willing to be your first search will return something that fits your scenario.
Let me know if you want specific info...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
December 1, 2011 at 9:59 am
isuckatsql (12/1/2011)
I assume the goal is to keep each table as small as possible?Based on the above assumption, i have some fields in a candidate profile table, job title(dates of employment), obviously these are VERY varied.
Should i leave them in the candidate profile table or create a seperate table,
JobTitle Table
CandidateId GUID
Job Title varchar(200)
and reference them based on a GUID, stored in the candidate profile table ?
Thanks
If you have not yet built this I would not use a GUID as your primary key unless you have an valid reason. They are real PITA to work with. If it simple a magic number an identity will be tons easier to deal with. There are a number of other issues with guids too. There have been tons of discussions on the boards here about reasons not to use them. I will try to dig some of them up.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 10:26 am
Here are just a few articles and blog posts referencing reasons to use something other than guids.
http://www.sqlservercentral.com/articles/Miscellaneous/alookatguids/2497/[/url]
http://www.sqlservercentral.com/articles/Editorial/71378/[/url]
http://www.sqlservercentral.com/articles/Guids/75506/[/url]
These are just a few in no particular order (my apologies to anybody if I missed your article or rant about guids).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 1, 2011 at 10:32 am
I agree with Sean in terms of the GUID. If you have a good reason, use them, if not, use an identity.
If you're worried about data size in a column, don't. The varchar is efficient in how things are stored.
In terms of the table, you do want them smaller if it makes sense, but it's a question of how often you need them as well as the pattern. If you have something that's varied by the row, say some rows have a title, some have a location, then you might move them to a child table.
However if you have data that's not often used or queried, maybe you want a horizontally partitioned table. So something like:
Create table Employee
( EmployeeID int identity(1,1)
, Firstname varchar(100)
, Lastname varchar(100)
, Title int
, Status int
)
go
Create Table Employee_Details
( EmployeeID int
, StartDate datetime
, EndDate datetime
, HiringManager int
)
go
The Employee_Details table is for those items that you rarely query.
You should also make sure you're not using SELECT * in queries, or make sure developers aren't doing that. Pull back the columns you need only.
December 1, 2011 at 10:51 am
isuckatsql (12/1/2011)
I assume the goal is to keep each table as small as possible?Based on the above assumption, i have some fields in a candidate profile table, job title(dates of employment), obviously these are VERY varied.
Should i leave them in the candidate profile table or create a seperate table,
JobTitle Table
CandidateId GUID
Job Title varchar(200)
and reference them based on a GUID, stored in the candidate profile table ?
Thanks
I would do this (and have for a recruitment portal I built):
CREATE TABLE candidate (cid bigint identity(1,1), firstName varchar(100) NOT NULL, lastName varchar(100) NOT NULL) --basically all candidate info that rarely changes and cannot be multiple
CREATE TABLE job (jid int identity(1,1), job_title varchar(255) NOT NULL, job_description varchar(MAX) NOT NULL) --job reference table with ids
CREATE TABLE candidate_job_history (jhid bigint identity(1,1), cid bigint NOT NULL, jid bigint NOT NULL, applied_date datetime NOT NULL, hired_date datetime NULL) --job/candidate link
The above is not exact, but gives a quick idea.
Jared
Jared
CE - Microsoft
December 1, 2011 at 11:34 am
Thanks for the feedback!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply