best way to create a potentail child record

Question

best way to create a potentail child record

yogiberr

Hall of Fame

Points: 3679
More actions
November 15, 2004 at 6:35 am

#85988

Hiya,
Sorry for the dodgy title.
I would like to create a “person” entity without necessarily attaching this “person” entity to a "company".
I have create d a schema that I believe will get me what I want, but I was looking for a 2^nd opinion J:

<schema>
tblPerson
personId
personName

tblCompany
companyId
companyName

tblCompanyPerson
companyId
personId
<\schema>

So, now I can create a “person” entity.Then, later if I want to atach it to a company, the I simply insert the personId into tblCompanyPerson.

Is this the approach that the more seasoned among you would take?

Many thanks,
yogi

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

This will do the trick for the business rule described. As long as it doesn't conflit with any other rule then you'll be fine.

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

good,

I just wanted to make sure that there weren't any "hidden" dangers

je vous remercie,

yogi

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 3

Hi

Yes this will work and it gives you the flexibility that a person could work for more than one company and a company could (of course) have more than one person working for it.

However another thought .... what if the person leaves a company? ...and moves to another one in your Company table? Don't know what you're going to do with this data but I bet it will be important to someone to know when they started/left/moved..

You might want to consider adding a StartDate and EndDate column to tblCompanyPerson. If you do this remember that all four of these columns could be the primary key for this table

Regards

Sam

yogiberr Hall of Fame Points: 3679 More actions · Answer 4

cheers Sam,

As an extension to this topic, how would you find out the “current” company or companies that the employee works for?

Would you check to see if the start date is not null and the endDate is Null?

Or maybe you'd add an “isCurrent” bit column to the table / use triggers.

I’d be keen to hear J

Thanks bud.

yogi

Sam Shiell Ten Centuries Points: 1373 More actions · Answer 5

Hi Yogi,

The "standard" way I'd do that would be along the lines of :-

WHERE EndDate IS NULL OR GetDate() BETWEEN StartDate AND EndDate

I'd steer clear of any "IsCurrent" flags or anything like that as they give you extra scope for things to go wrong.

You may not always need to know their current company. In my previous job we had additional tables that related to peoples accounts and often needed to calculate things based on the Company a person was at when certain things happened, nearly always in the past.

For that we used a date variable (that would have been either been passed as a parameter to a stored proc) or obtained dynamically from yet another table, and did the same "WHERE " the date "BETWEEN...."

For info, although I didn't like it the way my last job did history was to make StartDate default to GetDate() and EndDate default to "31 Dec 3000" (yuk!!!). But at least making the enddate always appear and be some ridiculous time in the future you never had to worry about NULLS

Cheers

Sam

yogiberr Hall of Fame Points: 3679 More actions · Answer 6

righto

I could pass timestamps in to denote when the employee started/ended.I don't need to know if an employee started>resigned >restarted.So that should do fine.

that remonds me, i must make plans for year 3000 celebrations

thanks,

yogi

Pat Tormey Grasshopper Points: 15 More actions · Answer 7

As you see all the solutions are getting harder. That' s a clue that you are going the wrong direction. Correct answers make life easier.

The simpliest way is to create an "Unassigned" company entiry and use it's primary key as your default, Then when you assign the person later you can list the people in "Unassigned", or report against "Unassigned".. much easier

The concept of making multiple fields into the primary key over complicates things. All you need is for them to be indexed uniquely. DON'T Put baggage in the primary key.

In fact Primary keys should be ALWAYS be artificial. They are just the glue that holds your structure together. see http://www.4square.net/4square/Default.aspx?tabid=31 for more detailed resoning..

Pat Tormey New Hampshire USA

yogiberr Hall of Fame Points: 3679 More actions · Answer 8

hi Pat.

I'm using the sqlServer "identity" as the PK for my entities.

So essentially, If I create a new employee and do not assign to a company, then (if the "default" companyId is 1), then I should simply insert the following values into tblCompanyPeople:

companyId

personId

<\schema>

companyId = 1

personId = whatever value SCOPE_Identity provides (as a result of creating the new “employee” ?)

Hope I’ve understood correctly

Cheers,

yogi

Pat Tormey Grasshopper Points: 15 More actions · Answer 9

Exactly Right.. You'll need (and want) an entry in the Parent Table (Company) that has the PK of 1 and some name like "UNAssigned" where no real company will ever have that Name.

The rest is easy..