----------------------------------------------------------- New Message on BDOTNET
----------------------------------------------------------- From: SriSamp Message 1 in Discussion Hi Group, While working on the .NET CF specifications, I happened to look at the database schema design for RM.NET and I have some suggestions. (1) In the tblUsers table, the name fields need not be NVARCHAR(100), since it very rare to find names such long (like middle name). Can we shorten this further, since we would be saving on space as NVARCHAR occipies twice the amount of storage and also hold relisitic data. (2) In the tblAddresses table, the address columns can be reduced in length for Line1, Line2 and City. This is again purely for ensuring space optimization and to hold realistic data. (3) In the tblUsers table, boolean fields like IsLoggedOn, need not be NCHAR, since the values of these are known and fixed (Y and N). We can just have it as CHAR(1). This reduces storge space and promotes standardization in using Unicode data types. (4) In the table tblResourceType, boolean fields like IsValid, need not be NCHAR, since the values of these are known and fixed (Y and N). We can just have it as CHAR(1). This reduces storge space and promotes standardization in using Unicode data types. (5) As a general rule, boolean fields can have CHECK constraints defined on them on the possible values. This way, we will not import wrong data into these tables in the event that we support some external web service supplying data to RM.NET. (6) On the tblResources table, we should implement some CHECK constraints on Quantity and Available fields. Also, I think it would be a better option to have a seperate column called NumIssued which is incremented each time the IssueDate in tblReservations is filled. This way, the Available column can be a SQL Server computed column of Quantity - NumIssued. This way, if we change the Quantity column later on (by presumably ordering more resources), the Available column will be automatically computed. (7) We need to implement some CHECK constraints on the tblReservations table against the date fields. This way, data consistency can be maintained. (8) The tblStaticDataType table has the Description as NTEXT, whereas the tblResources has it as NVARCHAR(1000). Let's standardize on the data type of Description fields as NTEXT and with the text in row option turned on for the table, so that SQL Server does not create NTEXT fields in seperate pages, but rather puts data along the row. Later, when the length increases, SQL Server automtically manages the split of pages. These are just some of my thoughts on a quick review of the design. I think all these can be implemented without altering any of the other layers (provided the data tier teams considers these valid :-)) If time does not permit, we can take these up in the next version update to RM.NET too... Srinivas Sampath MVP - SQL Server ----------------------------------------------------------- To stop getting this e-mail, or change how often it arrives, go to your E-mail Settings. http://groups.msn.com/BDotNet/_emailsettings.msnw Need help? If you've forgotten your password, please go to Passport Member Services. http://groups.msn.com/_passportredir.msnw?ppmprop=help For other questions or feedback, go to our Contact Us page. http://groups.msn.com/contact If you do not want to receive future e-mail from this MSN group, or if you received this message by mistake, please click the "Remove" link below. On the pre-addressed e-mail message that opens, simply click "Send". Your e-mail address will be deleted from this group's mailing list. mailto:[EMAIL PROTECTED]
