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 |
|
seanmmcc
Starting Member
1 Post |
 Posted - 2002-10-12 : 18:56:11
|
| i am working on a school project i have spent some time comming up with the most flexible model i can think of knowing that our next three projects will build off the model we create here. i was hoping for your two cents on what i've come up with.the mission was to create a db for the national association of realtors. there could be agents that worked for brokers and companies that brokers worked for. well i am currently under the assumption that agents could work for multiple brokers and maybe even one broker could work for multiple companies. all of their data being simlar i attempted to keep it all together. down the road we will be reporting on agent/broker/company sales... we also need keep track of all propertys agents sell and the detail info of the sale...heres what ive come up with...Property_Sales Property_ID (PK) Agent_ID(FK) Asking_Price Selling_Price Listing_Date Sale_Date Age_Code (FK)– (from type_data table) Property_Code (FK)(from type_data table) Status_Code (FK)(from type data)NRA_User User_Id (PK) Business_Name First_Name Last_Name Type_Data_Code (FK comes from the type_data table)Employed_By (allows an agent to be associated to multiple brokers) Employed_By_ID (PK) User_ID (FK) Employer_ID (FK)TYPE (telephone/ address/ NRA_user / Property_Style / Property_Age / Sales_Status ) Type_Code (PK) Type Type_DescriptionTYPE_Data (agent/broker/company/home tel/work tel/ cell tel/ SFH / Condo/ Duplex / Sold / Contract Pending) Type_Data_Code (PK) Type_Data Type_Code (FK) Type_Data_DescriptionAddress Address_ID (PK)FK_ID (FK) (will associate itself to a primary key of a property or a user, or a company...)Type_Data_Code (FK)(come from type data table)StreetSuite_NumberCityStateTelephoneTelephone_ID (PK)Area_CodePrefixSuffixFK_ID (FK)(will associate itself to a primary key of a property or a user, or a company...)Type_Data_Code (FK)thanks for your inputsean |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-10-13 : 16:47:42
|
| Well Sean, we have a standard policy here of not answering homework questions, but because you've actually done the work and are asking for opinions rather than asking us to do the work for you (as so many have done in the past) I think your situation is different.So, here are a few comments for your consideration...1) In your dealings with agents/brokers/companies, you need to ask yourself some questions about what you'll need out of that. For example, will you need to know all the agents that work for company x? Or will you need to update all agents who work for broker Y and reassign them to broker Z? You're really getting into a topic here known as a tree structure or hierarchies. There are many different ways of doing this, and which you choose depends on what you're going to have to do with it later. You might want to do an article search here on SQLTeam for trees and see some options.2) Your tables all have PK's with the word ID in them. My assumption is that these will all be IDENTITY fields. In some cases that works well, but in others it won't. For example, your Employed_By table has Employed_BY_ID as the PK, and then UserID and EmployerID. This would technically allow duplicate records in this table, where the UserID-EmployerID combo was the same but they have different PK's. Instead, you might get rid of the Employed_BY_ID field, and use UserID-EmployerID as a 2-part PK for this table.3) For Telephone, why did you separate Area Code, Prefix and Suffix each into its own field? What benefit do you expect to gain from this? |
 |
|
|
|
|
|
|
|
data model question