| Author |
Topic |
|
egil
Starting Member
16 Posts |
 Posted - 2008-06-11 : 09:04:04
|
HiI have about six different entities that can have zero or more note entities associated with them. The easy way to do this is obviously to have a different "note" table for each of the entities i.e. WorkItemNote, CustomerNote etc.. But I would much rather have a single "note" table since they would all be identical, so I came up with this design:CREATE TABLE WorkItem ( WorkItemGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity)CREATE TABLE Customer( CustomerGuid uniqueidentifier PRIMARY KEY DEFAULT (newid()), -- rest of table declaration removed for bravity)CREATE TABLE Note( NoteId int IDENTITY(1,1) NOT NULL PRIMARY KEY, ReferenceGuid uniqueidentifier NOT NULL, Text ntext NOT NULL, -- rest of table declaration removed for bravity) This way I can get notes associated with a given entity, either Customer or WorkItem, by just selecting from the Note table with its WorkItemGuid or CustomerGuid.My question is: Is this the best approach to what I am trying to accomplish? (ps: Apologies if "many to one" is not the right terminology)Regards, Egil. |
|
|
Dallr
Yak Posting Veteran
87 Posts |
Posted - 2008-06-11 : 09:59:53
|
| You are correct in saying you would need one note table to capture all the information on the many side. PS:I have just included the Primary keys on the tables on the one side like yourself. tbl_WorkItemWorkItemID (PK)tbl_CustomerCustomerID (PK)SomeOtherTableSomeOtherTbl_ID (PK)tbl_NoteNoteID (PK)WorkItemID (FK)CustomerID (FK)SomeOtherTbl_ID (FK)Dallr |
 |
|
|
egil
Starting Member
16 Posts |
Posted - 2008-06-11 : 10:15:45
|
| Dallr, is it preferred to have FK relation like you have in your set up at the cost of the extra rows? Or is it just that you do not like to use uniqueidentifier? |
|
|
|
Dallr
Yak Posting Veteran
87 Posts |
Posted - 2008-06-11 : 10:35:25
|
| Well, I come from a Microsoft Access background and have been developing databases for the last 5 years on this platform. Generally, this is how we setup the structure you are proposing. From an SQL server standpoint I am not sure if what your originally proposed is the ideal option. I guess someone else with more data modeling experience in SQL Server can give some input as to your option.Dallr |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-06-11 : 11:33:02
|
| I don’t really like this design, because there is no way to enforce declarative referential integrity to the parent table for each note. I don’t see any problem with having multiple note tables.CODO ERGO SUM |
|
|
|
egil
Starting Member
16 Posts |
Posted - 2008-06-11 : 12:26:10
|
| Michael, you are probably right. I am certainly not a database guy, so I might be trying to optimize were there is nothing to optimize on. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-06-11 : 13:39:15
|
| I wouldn't worry too much about the declarative referential integrity with this. It would be a trade-off of administrative and development issues either way you go.I do wonder about including the Identity pkey on the Notes table when you already have GUIDs....that's pretty redundant.e4 d5 xd5 Nf6 |
|
|
|
egil
Starting Member
16 Posts |
Posted - 2008-06-11 : 14:20:35
|
| blindman, the PK in the notes table allows me to delete/update existing notes. I can not identify a note by the reference guid since there can be many notes with the same reference guid. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-06-11 : 16:45:56
|
| Ah. I see what you are doing now.e4 d5 xd5 Nf6 |
|
|
|
Dallr
Yak Posting Veteran
87 Posts |
Posted - 2008-06-11 : 23:03:58
|
| I was out for the rest of the day. Another option is to have each table have their own note attribute(field) within the same relation(Table). ThereforeCustomerCustomerID (PK)CustomerNoteWorkOrderWorkOrderId (Pk)WorkOrderNote Dallr |
|
|
|
egil
Starting Member
16 Posts |
Posted - 2008-06-12 : 03:50:47
|
| Dallr, what type would WorkOrderNote/CustomerNote be of? Remember, there can be many notes per Customer and WorkOrder, not just one. |
|
|
|
Dallr
Yak Posting Veteran
87 Posts |
Posted - 2008-06-12 : 04:56:04
|
| Oops, Sorry Egil I forgot each customer/workorder can have many notes. In light of that scrap my last comment. You can still use the structure provided in my first post. I am out the country for a few days and would not have internet access until i get back. Will check back in then. Dallr |
|
|
|
egil
Starting Member
16 Posts |
Posted - 2008-06-12 : 05:13:12
|
| hehe no problem Dallr, thanks for the input and happy traveling!Regards, Egil. |
|
|
|
|
Many to One relation - is it a good idea?