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 |
|
Muten79
Starting Member
7 Posts |
 Posted - 2008-06-18 : 07:38:00
|
| Hi!I'm not sure how to resolve this problem:I've got 4 tables:- Table1 (UniqueCode, Col11, .. , Col1N)- Table2 (UniqueCode, Col21, .. , Col2M)- Table3 (UniqueCode, Col31, .. , Col3X)- Table4 (UniqueCode, Col41, .. , Col4X)Each TableX has the same column: UniqueCode and its column's value can't be duplicated in the tables.Ex.Insert Table1 Values ('Code1', 'Ex1', ... )Insert Table2 Values ('Code1', 'Ex2', ... ) -> Error!1) Should I control this programming a Trigger?2) Would be better to do a new Table? TableUnique( UniqueCode, IdTable1, IdTable2, IdTable3, IdTable4) .. for example??Thanks!!! |
|
|
pootle_flump
1064 Posts |
Posted - 2008-06-18 : 08:07:22
|
| Depends on what is being stored in these tables.One possibility is super type\ sub type but without more info it is difficult to be certain. |
 |
|
|
Muten79
Starting Member
7 Posts |
Posted - 2008-06-18 : 11:27:44
|
| Thanks for your reply.The four tables compound the structure (work center, area, department and sub-department) that could have a company (it'll be an accounting program).The unique code is an 'imputation code' that identify the distinct pieces of the structure, this code is unique for these tables. The user defines it and can change it.I've thought in a GUID. I'm not used to work with GUIDs but I think it's similar to an Identity, the database gives you the value and I need the user set this value.I haven't collected the requirements, so I can't do very much.Thanks, again, for your attention. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-06-18 : 11:56:56
|
| Do these four tables share the same columns? I don't think they do based on your definition but just to be explicit....Do these four categories form a hierarchy (i.e. a sub-department is under a department, a department under an area etc.)?I wouldn't bother figuring out your key data type just now. By the sounds of it you will want to use a surrogate key but we can figure that out later. The important thing now is to understand the requirements. It may be that you need five tables, it may be that one will suffice. There could be several ways to satisfy your requirements - just bare with me a bit. |
|
|
|
Muten79
Starting Member
7 Posts |
Posted - 2008-06-18 : 13:59:43
|
| Hi pootle, thanks for your reply.You're right, the tables are in hierarchy, like you said (sub-dept under dept, dept under area and area under work-center). I use a surrogate key for them.I thought in a fifth table that contained the unique code:UniqueCodeTable(UniqueCode)WorkCenter(IdWorkCenter,..., UniqueCode)Area(IdArea,...,UniqueCode)Department(IdDept, .. , UniqueCode)SubDepartmen(IdSubDept, .. , UniqueCode)In this case, I shouldn't allow the same UniqueCode in child tables through triggers.It sounds a bit strange, doesn't it?WorkCenter table is used for another application and it could contain null-value. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-06-18 : 15:59:15
|
Not strange at all  .Just to repeat - Do these four tables share the same columns?Anyway - to help things tick along you might consider reading up on sub types and super types - this is a database design strategy for this type of problem. Jeff has written an excellent article on these which describes them in OOP terms. HOWEVER - whether or not this is a good design choice depends on the answer to the above question. Note - you should not need triggers here - this can be done with standard referential integrity.http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server |
|
|
|
Muten79
Starting Member
7 Posts |
Posted - 2008-06-18 : 18:32:13
|
| The tables have different columns. WorkCenter table has many columns (it's been used by other application), and the others tables have only two (or three) columns (Identifier -AND/OR- UniqueCode, Name).I'll take a look of the article!Thank you very much. |
|
|
|
Muten79
Starting Member
7 Posts |
Posted - 2008-06-19 : 03:01:37
|
| I've read the article and I think it should work!I'll do it with persisted computed columns.Thanks a lot. :) |
|
|
|
|
|
|
|
|
Unique key in 4 tables