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 |
|
masteripper
Starting Member
25 Posts |
 Posted - 2010-07-29 : 03:04:51
|
| I am planning for a project and i would like to have my tables like These:The Clients TableClientID Name1 Client12 Client23 Client3...........N ClientNThe Suppliers Table1 Supplier12 Supplier23 Supplier3.............N SupplierNAnd the Banks Table1 Bank12 Bank23 Bank3............N BankNNow the Info On which Client/Supplier uses which bankNote C for Client ,S for SupplierClientSupplier BankIDC1 2C1 3S2 1S3 2And so on....Consider that there should be a separate form for the Client and one for the Supplier .On each of these forms i should have a datagrid for entering the bank information.I could easily have a Clientbank and a SupplierBank table but i want to know if this scenario can work. |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-07-29 : 03:19:59
|
| How about separate tables for ClientBank and SupplierBank - i.e. the tables which link clients / suppliers to banks.Presumably a Client may use more than one back? if not just add a BankID column to the Client table. Same for Supplier.Do NOT combine the ClientID with a "C" on the front (as per your final example), at the very least use a separate column for "Source" - i.e. "C" or "S".But that means that you can't then easily put a Foreign Key relationship on the ID column to join it back to the correct table.If you want a single Client / Supplier Bank Link table then you could make two table, one for each (as I described above) and then create a VIEW that combines them together (with "C" or "S" [Source] indicator too) |
 |
|
|
masteripper
Starting Member
25 Posts |
Posted - 2010-07-29 : 03:33:12
|
| So it is much better to create two separate tables one for clients and one for suppliers.On the other hand considering the scenario of separate column for the "source" how could it be possible to put a foreign key relationship.The idea of the view is good but it falls to the case of 2 tables....so ...I'd like to say that this is something i am thinking as a way to reduce "unnecessary" tables ......i know it is strange and probably it would cause more problems than solve other but it was spinning in my head for some time and i would like to know all possible approachesThanks for the quick response and i am waiting for some more comments.P.S Consider the case that could easily have a lot of of "sources" and "related"Something like thisSomeComplexID..... RelatedCS001.............. 1CS001.............. 4CS007.............. 1CS008.............. 9CE008............. 18DR008............. 23AS038.............. 9and so on |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-07-29 : 11:55:47
|
A couple of suggestions, First, if you have not read an Introduction to Database Systems, I'd suggest you read it (I suggest everyone who touches a database read it  ). Second, Surrogating could/might be considered bad, but SQL is very good at it. So, it can be a powerful technique. But, generating "IDs" based on an entity it comes from is most definitely a bad idea. As Kristen suggested, creating a relationship table is the way to go. That will Relate Entities to other Entities without having to create functional dependencies and make it much easier to properly constrain your database. |
|
|
|
|
|
|
|
|
Some advice on my tables organization scenario