SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Database design help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Creakyknees
Starting Member

United Kingdom
3 Posts

Posted - 12/19/2013 :  14:26:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 12/19/2013 :  14:35:41  Show Profile  Reply with Quote
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=47943

I guess the link would help...

Be One with the Optimizer
TG

Edited by - TG on 12/19/2013 14:36:13
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 12/20/2013 :  03:29:43  Show Profile  Reply with Quote
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 FormItems

Depends how much flexibility you need on the forms side. Its certainly a more complex programming task ... but provides a lot of time saving downstream.
Go to Top of Page

Creakyknees
Starting Member

United Kingdom
3 Posts

Posted - 12/20/2013 :  06:02:18  Show Profile  Reply with Quote
Thanks for the help so far, I an getting a couple of good ideas from the replies. Thanks everyone.
Go to Top of Page

Creakyknees
Starting Member

United Kingdom
3 Posts

Posted - 12/26/2013 :  14:14:16  Show Profile  Reply with Quote
Ok, I will try and be a little more specific:

I have the following tables:

tblLandlord
PK IDLandlord
Forename
Surname
CompanyName

tblAddresses
PK IDLandlord
FK IDAddress
AddressLine1
AddressLine2
AddressTown
AddressPostCode
AddressCounty

tblContact
PK IDLandlord
CompanyAddressLine1
CompanyAddressLine2
CompanyTown
CompanyPostCode
CompanyCounty
CompanyTelephone
CompanyMobile
CompanyFax

Then a form for each Paper From that has to be completed like Boiler Checks:

tblBoiler
FK IDAddress
Date
Pass/Fail
Conducted by
Action taken

I 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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5914 Posts

Posted - 12/27/2013 :  11:10:46  Show Profile  Reply with Quote
>>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 Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000