Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
 Posted - 2002-06-26 : 07:54:21
|
I need some help with a design issue.Let’s say you are building a database for a homebuilder (in the USA). They hire all manner of contractors to do the electric, plumbing, carpentry, etc. So you have a table ...create table workforce ( MHIC varchar(11) not null primary key, Trade varchar(10) not null ) Now, lets say, you have built the database, built the app and all is well. Then the end-user comes to you and says, "You know, sometimes the same dude(tte) will do more than one thing, like (s)he may be an electrician and a plumber. (S)He’ll have two different MHIC numbers for (her)his two different trades. I want the system to keep them separate, as it does, but for the one report, I’d like to report on the person, not the MHIC number."You say, "Well, how am I supposed to know which MHICs are connected in this fashion. We don’t carry any data that will tell me that (like SSN or some other natural person based key)."He say, "Oh, it’s only a few people. I’ll tell you which ones."So how do you model that? Now, this is just an example: I don't know if the same person can really have two different MHIC numbers, but it illustrates my problem pretty well. A major database redesign is out of budge scope, so this has to be a duct-tape solution. I can add new tables to the schema and/or add a column or two to Workforce, but nothing major. I have a few ideas, but I'd like to see what people have to say on this.Thanks.<O> |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-26 : 08:33:08
|
How about adding a ParentMHIC field into the workforce table...I know it's the short answer (and one of the one's you've probably already thought about )...PeaceRick |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-26 : 08:37:06
|
| Yeah, I did think about that, but the various MHICs for a person are not hierarchical.<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-26 : 08:41:35
|
| Assuming you don't want to add a surrogate key, why not just add a name column (or first and last name columns)? You then have 2 choices:1. If MHIC's are unique for each trade, then keep it as your primary key;2. If not, then change the primary key to be MHIC, TradeAll of your reports can then be grouped by the name column(s).JMHO |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-26 : 08:44:44
|
| How about having a composite key of (MHIC, Trade). Am sure you might have thought about this too.i have come across this kinda scenarioa. i had to store the transaction's happening on a stock. and for a pk i couldnt use the transaction no coz different type of transaction had a same no. i ended up having a composite key of (transactionno,type) . not too much to worry too on performance issues, as indexes would be used incase i query on both the fields or either of it.my 2 cents!SNIPED by the Master Sniper!!!!-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo EmersonEdited by - Nazim on 06/26/2002 08:56:40 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-26 : 08:57:41
|
Hum....its tricky because in my head I an translating my example problem into my actual problem .Let me add two things.-There are two John Smith's. One John Smith is a crane op. The other John Smith does siding and roofing (and thus has two MHICs).-The composite key thing, may work for me, but for sake of arguement, can we pass on that one? Let's say trade is not null and some people don't have a trade...they are just 'casual labor'...I am thinking the solution is going to need to involve a second 'mappings' table, but I am not sure how to construct it.Maybe I am asking too much by not providing my actual problem schema and using this example. Sorry about that...create table peoples ( PersonID int not null, MHIC varchar(11) not null )alter tableadd constratint pk_peoples primary key (personid,mhic) I feel like it needs to be something like this, but there are problems with it. I need to calculate this contrived personid somehow.<O> |
|
|
|
MakeYourDaddyProud
184 Posts |
Posted - 2002-06-26 : 08:59:46
|
| Is it possible to abstract the perception of the MHIC column in your workforce table? This could now be interpreted as a top level trade code that can spawn 2nd level MHIC codes thus...Create table MHIC_Level( MHIC varchar(11), MultiTrade_MHIC varchar(11) with null)primary key is MHIC,MultiTrade_MHICThis allows you to both specify a trader person with just 1 trade or one with several tradesCreate table Trader_MHIC_Level( MultiTrade_MHIC varchar(11), TraderName varchar(50), TradeDescription varchar(100))primary key is MultiTrade_MHIC... so a single trade trader with MHIC code of, say, CP1234 present in workforce table could have in MHIC_Level...MHIC_Level----------CP1234, MCP1234...Trader_MHIC_Level-----------------MCP1234, "Daniel Small MIAP", Electrician... or a multi trade trader with MHIC code of, say, AM4567 present in workforce table could have in MHIC_Level...MHIC_Level----------AM4567, MAM45677AM4567, MAM45678AM4567, MAM45679...Trader_MHIC_Level-----------------MAM4567, "Daniel Small MIAP", ElectricianMAM4568, "Daniel Small MIAP", CarpenterMAM4569, "Daniel Small MIAP", Groundworker... obviously repeating group of name (Daniel Small MIAP) could be replaced by a generated SSN/Unique ID to point to a table of Traders...Using relational model SQL you can join "thru" the tables group by the name of each individual for reporting.HTH<<monet makes money>> |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-06-28 : 20:22:46
|
| Jay,Isn't this just a variation on a Parent-Child relationship in that the majority of parents will have only one child (1-1) but a few will be 1-N? So, make a Person table with an appropriate key (Rob, close your eyes for a minute... it might be an IDENTITY!). Then create your workforce with MHIC and Trade and PersonID. Then you can just join these like any other 1-N relationship and group on PersonID for reporting. |
|
|
|
|
|
|
|
|
My Primary Key isn't unique...sometimes!?!?!