Hi, I'm developing a new application, and a new database to go along with it. I'm trying to develop version 1 with an eye towards LINQ To Entities so I'll be positioned to take advanteg of it when it ships. I don't have the luxury of having a DBA, so I have to create the database structure myself, and I'm not a database expert :-(
In my database I have a Contacts table (which is abstract), a Persons table, and a Companies table. Basically, a contact can be a person or a company, and I'll never create a contact itself - it's just a base "class". So my contact table looks like this:
ContactID uniqueidentifier <- primary key
WebSite varchar(100)
...
TS timestamp <- for concurrency
The company table looks like this:
ContactID uniqueidentifier -< primary key, and matches ContactID in the Contacts table
CompanyName varchar(50)
...
TS timestamp <- for concurrency
Now, here are my questions:
1) This first question is most likely unrelated to LINQ To Entities, but should I use the same primary key for both the Contacts table and the Companies table (i.e., ContactID) or should the Companies table use a CompanyID as the primary key and then have another column named ContactID that links back to the ContactID in the Contacts table Because this is a 1:1 join (well, sorta - there will never be more than one company with the same ContactID but there will certainly be records in the Contacts table without an associated company record) I'm thinking I should use ContactID as the primary key for both tables.
2) The samples I've seen would suggest having a discriminator column in the Contacts table. However, pre-LINQ I would just do something like SELECT ... FROM Companies INNER JOIN Contacts ON Companies.ContactID = Contacts.ContactID. No need for a discriminator in order to retrieve all companies. Is the discriminator mandatory when I go to LINQ (in which case I'll put it in right now even though I won't really use it yet) I realize it's used if I want to just select all contacts and it goes about creating a polymorphic list with some Person objects in it and some Company objects in it. I'll never be doing that - I'll either be retrieving a list of Companies or a list of Persons.
3) In terms of RI, I want to set this up with cascaded deletes so both tables get deleted (from) at the same time. Should I cascade from Contacts to Companies so when I delete the contact record the associated company record gets deleted Or should I cascade from Companies to Contacts so when I delete the company record the associated contact record gets deleted Or does LINQ just figure it all out and I don't need to cascade deletes at all
Thanks in advance for any best practices and ideas you can offer.
-Eric Harmon