> Oh,and on the doctors, nurses example, I'd do it like > this: > tblStaff
> StaffID PK > StaffTypeID FK > tblStaffType(s) > StaffTypeID PK > StaffTypeName > etc.. etc.. > Or just forget the stafftypeid and use stafftypename as > the PK I agree with stafftypeid -- you always want to be able to rename the staff type if someone doesn't like the name given, and it's a lot easier to do that when the name is not a primary key. Same reason I object to using username as a primary key in a login / user table. It makes it difficult at best to reassign a user's username later. Hell, my schemas typically even break out contact information this way -- don't ask, it's a pet peeve -- I hate having day, evening and cell phone numbers all in a table of people or entities... So I end up with something similar to this in my schemas: ENTITY entityid ENTITYTYPE entitytypeid, entitytypename ENTITYRELATIONSHIPTYPE entityrelationshiptypeid entityrelationshiptypename ENTITYRELATIONSHIP parententityid childentityid entityrelationshiptypeid alternatejobtitle (nvarchar) CONTACTMEDIUM (phone,email,address -- corresponds to a table) contactmediumid contactmediumname CONTACTTYPE (home,office,cell,sales,support, etc) contacttypeid entitytypeid contacttypename CONTACT contactid entityid contactmediumid contacttypeid CONTACTPHONE contactid (I typically make this a primary key instead of having a separate primary key for medium related information) phonenumber phoneextension .... CONTACTADDRESS contactid address addresscity addresscountry addresspostalcode It's not always this normalized, but I like this normalization at least conceptually... It allows the assignment of as many contact elements of any given type to any given entity (company, department, person) without any arbitrary limitations of a given entity only having (home,office,cell) or (sales,support,front desk) contact types. It also allows a person to belong to more than one department or company in different capacities (corporate liason between entities - an "errand boy" who supports multiple departments) and it allows the people using the application to expand the content as needed without requiring programming assistance. The only thing it leaves to be desired for me is the idea of primary contact points -- both between a company entity and an employee (primary contact for sales - primary contact for support - primary general contact (front-desk / receptionist)) or primary contacts for an individual (primary phone number, primary email address, etc... -- preferred contact medium (phone,email,postal) is stored as a foreign key to the contactmedium table in the entity table)... I don't want to create foreign keys in the entity table because that places an arbitrary limitation on the contactmedium table. I've tried putting an "isprimary" column in the contact table, but don't much care for that either... just feels odd to have all those records with 0's ... I guess the only thing I haven't tried is using a cross-reference table CONTACTPRIMARY contactid entityid There could be no more than one record for each combination of entity and contactmedium. My problem with that is I couldn't create a unique constraint on the contactprimary table to ensure there isn't more than one record per medium per entity. At least not without duplicating the foreign-key relationship between the contact and the contactmedium tables. I dunno... I'm probably over-thinking the whole thing -- it's just a pet peeve of mine, I don't know why. s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? add features without fixtures with the onTap open source framework http://macromedia.breezecentral.com/p49777853/ http://www.sys-con.com/author/?id=4806 http://www.fusiontap.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:200787 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
