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 |
|
CaioToOn
Starting Member
6 Posts |
 Posted - 2009-07-14 : 18:36:28
|
| Hello there!I always get in problem with this kind of needing.I'm Brazilian, if someone do not understand what did I mean, please tell me, I probably misused a term.The current problem is:I'm designing a database to control the cash movement of a company.The company have money on 2 kind of sources: bank accounts and safe boxes. I have more than one account bank, and more than one safe box. Considering it, the movements should specify in which of the sources it has happened.Until now, I would have 3 entities:ACCOUNT_BANK, SAFE_BOX and MOVEMENT.Which is the better layout to deal with integrity reference without breaking any NF Rules in this case?A initially approach would be:ACCOUNT_BANK- id[int, PK]- description[text]- account_no[int]- bank[text]- source_uid[int, FK with SOURCE_UNIQUE_ID.uid]SAFE_BOX- id[int, PK]- address[text]- source_uid[int, FK with SOURCE_UNIQUE_ID.uid]SOURCE_UNIQUE_ID- uid[int, PK]MOVEMENT- id [int, PK]- time[datetime]- value[currency]- source[int, FK with SOURCE_UNIQUE_ID.uid]This solution can guarantee the reference integrity in one direction. For example, if I want to know all the movements that happened with a single source, I can easily make a double join: ACCOUNT_BANK or SAFE_BOX > SOURCE_UNIQUE_ID > MOVEMENT. The problem appears again when I need to identify the source of a given movement. I need to make a inner join to both the account_bank and safe_box tables, since I don't know which type of source I'm dealing with...So, which is the better solution to this case? I could also put a field in the SOURCE_UNIQUE_ID specifying the type of the source, but this will make queries much more complex, since I will need to make ifs...Thank you in advance for the help,CaioToOn! |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2009-07-15 : 15:38:15
|
| "The problem appears again when I need to identify the source of a given movement. I need to make a inner join to both the account_bank and safe_box tables, since I don't know which type of source I'm dealing with..."Just do two OUTER joins. Example:SELECT ...FROM MOVEMENT MLEFT OUTER JOIN ACCOUNT_BANK AON M.source_uid = A.source_uidLEFT OUTER JOIN SAFE_BOX BON M.source_uid = B.source_uid... |
 |
|
|
CaioToOn
Starting Member
6 Posts |
Posted - 2009-07-15 : 16:00:30
|
quote:
Originally posted by dportas
"The problem appears again when I need to identify the source of a given movement. I need to make a inner join to both the account_bank and safe_box tables, since I don't know which type of source I'm dealing with..."Just do two OUTER joins. Example:SELECT ...FROM MOVEMENT MLEFT OUTER JOIN ACCOUNT_BANK AON M.source_uid = A.source_uidLEFT OUTER JOIN SAFE_BOX BON M.source_uid = B.source_uid...
Hi, DPortas.Thank you, but let's assume that I have a movement and I want to take, with a simple query, the ID of the source.How may I accomplish this task with your example? I don't know which ID to take between the two options: A.id or B.id.Note that I don't want the source UID, I want the source ID. It looks silly, but in other cases I have the needing of getting others fields.Thank you!CaioToOn! |
|
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2009-07-19 : 13:29:55
|
quote:
Originally posted by CaioToOnThank you, but let's assume that I have a movement and I want to take, with a simple query, the ID of the source.How may I accomplish this task with your example? I don't know which ID to take between the two options: A.id or B.id.
COALESCE(A.id,B.id) AS id |
|
|
|
CaioToOn
Starting Member
6 Posts |
Posted - 2009-07-20 : 08:41:55
|
| Dude, you're the ONE!Forgive me the lack of knowledge.Thank you!CaioToOn! |
|
|
|
|
|
|
|
|
Best pratices: foreign keys on multiple tables?