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 |
|
Creakyknees
Starting Member
3 Posts |
 Posted - 2013-12-19 : 14:26:28
|
| Hi everybody, I am looking for someone who has a little time and the patience to help me with the design of MS Sql database. I am reasonably familiar with creating tables and queries, but it is the design of the structure of the database and the relationships I would appreciate help with and I want to get it right from the start.So here is my scenario:I want to write a database to store compulsory records for Landlords. Each landlord may manage several properties. For each property the records must be kept for a minimum of 5 years. Each record (such as fire safety checks, boiler inspections, servicability check) is currently a form that must be completed with many questions to each form. I envisage one table per form type. As far as possible I want to normalise the database with reusable tables storing addresses, etc. Any guidance, help and pointers would be greatly appreciated. Creakyknees. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-19 : 14:35:41
|
| Take a look through this post. I really liked the posts by byrmol. I thought it was a very concise explanation of the logical and physical db design process. I would suggest that you first go through the exercise he suggests then post back with some more details and ideally your first cut at a physical model. Folks (at least I) will be happy to help tweak the design.And I think taichi is good for creakyknees :)EDIT:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47943I guess the link would help...Be One with the OptimizerTG |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2013-12-20 : 03:29:43
|
quote:
Originally posted by Creakyknees
I envisage one table per form type.
consequence of that is that you have to change the database structure, and all the associated application and forms etc. if a new question is added to a form, or a new form becomes necessary.An alternative is to have FormHeader and FormItem tables. FormHeader might have FormID and Name/Description. FormItem would have a row for each question: what datatype the answer must be, max length for the answer, whether compulsory and so on. Then you need a FormAnswerHeader and FormAnswerItems. FormAnswerHeader will have FormID (from FormHeader), LandlordID, Date etc. and FormAnswerItems will have the answers to all the question in FormItems.If the form changes you just add a FormItem record for the new question. If there is a new Form then add a FormHeader and FormItemsDepends how much flexibility you need on the forms side. Its certainly a more complex programming task ... but provides a lot of time saving downstream. |
|
|
|
Creakyknees
Starting Member
3 Posts |
Posted - 2013-12-20 : 06:02:18
|
| Thanks for the help so far, I an getting a couple of good ideas from the replies. Thanks everyone. |
|
|
|
Creakyknees
Starting Member
3 Posts |
Posted - 2013-12-26 : 14:14:16
|
| Ok, I will try and be a little more specific:I have the following tables:tblLandlordPK IDLandlordForenameSurnameCompanyNametblAddressesPK IDLandlordFK IDAddressAddressLine1AddressLine2AddressTownAddressPostCodeAddressCountytblContactPK IDLandlordCompanyAddressLine1CompanyAddressLine2CompanyTownCompanyPostCodeCompanyCountyCompanyTelephoneCompanyMobileCompanyFaxThen a form for each Paper From that has to be completed like Boiler Checks:tblBoilerFK IDAddressDatePass/FailConducted byAction takenI have tried to keep all data normalised and the tables reusable. The main table is tblLandlord which is currently linked to tblAddress (1 to many) and tblContact (1 to many). Are the relationships correct?Secondly how do I link each table such as tblBoiler to tblAddress for each location and what is the relationship I should use?As the paper forms may change over time, do I need another table in the DB to index the forms and their details?All the best, Creakyknees. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-12-27 : 11:10:46
|
| >>Are the relationships correct?Not quite. A landlord can have many addresses but an address can have only one landlord, right? If so then the address table would have IDAddress as the primary key and IDLandlord (in the address table) would be the foreign key (referencing the primary key of the landlord table). Same thing with Landlord -> contacts.Couple of business rule questions:- When a property is owned by 2 or more people are they all one landlord row in your table or are they represented by a row for each individual?- Over time as an owner sells a property and buys another and as contacts change is the history maintained in your tables or do you just want the data as of "now"?Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
Database design help