Discussion on:
View:
Show:
It is important to discussion the business operation(s) involved with the application/database you are designing. Be especially attuned to words like "usually" and "always...except", "rarely", and so on. These are the clues to the real needs of the database and to avoiding getting far along in design and development and encountering an exception that mandates major rework and/or redesign. I've found that when you ask users about the data they tend to think of the "normal" contents of a specific data item and forget about the exceptions. For example (put in programming terms): "usually this name is a 10 character text string". If ask enough questions you may find out that every once in a while the name is 40 characters long. Meanwhile, you may have designed for a limit of 10 (or even 20) characters.
if you find yourself with tables named something like MapType or ObjectType, you've probably got something too generic. although this will seem appealingly elegant at first glance, engineers will curse your name for the ambiguity you created in the model, the perf hotspots you caused in overloaded mapping tables and the data migration nightmare which awaits. do your business logic homework and skip the temptation to give yourself easy outs.
This is really an extension of point 1. You want descriptive names for objects but those tend to be quite long and people don't want to type them too often.
Some RDBMS's provide a mechanism for creating synonyms for objects. If the RDBMS you are using allows this then for objects that are referenced often give the object itself a long descriptive name but provide a short alias synonym that developers can use in their code. This keep the developers happy and, more importantly, reduces the probability of typos.
Stephen
Some RDBMS's provide a mechanism for creating synonyms for objects. If the RDBMS you are using allows this then for objects that are referenced often give the object itself a long descriptive name but provide a short alias synonym that developers can use in their code. This keep the developers happy and, more importantly, reduces the probability of typos.
Stephen
Metadata is data about data. It's your definitions, business rules &c. It makes your design process a lot easier (espcially if you've got different designers working on different parts of the application or you are looking to integrate products together) if from the start you have created and maintained a metadata repository. It also makes maintenece easier, when you come to make a change 6-18 months down the line you can make sure that you know what each data item represents in the real world and understand fully what the repercussions of your change are going to be.
Meta data is also useful in providing an interface between the technical people doing the design and development (who aren't experts in the business side of things) andthe business people who are demanding the system and will be using it (who aren't experts in the IT).
Stephen
Meta data is also useful in providing an interface between the technical people doing the design and development (who aren't experts in the business side of things) andthe business people who are demanding the system and will be using it (who aren't experts in the IT).
Stephen
Reporting, depending on your industry this can be one of the most critical components of you database design. Reporting your data is always important to someone. It may just be someone in the marketing department who cares, or it may be your executive staff.
Fudge Factor is important and reporting may depend on it. You will always need an extra date, or data flag.
Also, it's always good to know upfront what kind of reports are expected at the end, so ask your business analyst or project stakeholders to provide you with mock-ups.
Fudge Factor is important and reporting may depend on it. You will always need an extra date, or data flag.
Also, it's always good to know upfront what kind of reports are expected at the end, so ask your business analyst or project stakeholders to provide you with mock-ups.
That can be handy for reporting systems that have a small number of users. As the number of users gets larger, they start getting into each other's "extra fields."
As the systems get larger, It's better if these users take local copies of the data (and not hold locks that keep the front line people out) and put in their own reporting-specific fields.
As the systems get larger, It's better if these users take local copies of the data (and not hold locks that keep the front line people out) and put in their own reporting-specific fields.
I can't think of a common database that does not cope with alter table. That rule seems to apply to flat files with fixed record lengths.
Even then it is hard to determine what the fudge factor should be. I remember an old system with a fixed length array (Oh Dear Me). The original designer thought that providing double what the business thought they needed would do the trick. This quickly proved to be inadequate.
Maybe another rule would be to understand the business well and be criticalof what you are told by users.
Even then it is hard to determine what the fudge factor should be. I remember an old system with a fixed length array (Oh Dear Me). The original designer thought that providing double what the business thought they needed would do the trick. This quickly proved to be inadequate.
Maybe another rule would be to understand the business well and be criticalof what you are told by users.
This can be a complicated question. I always try to consider the risks and costs associated with this. I generally side with normalizing because it saves me so much development time down the road.
One such place de-normalizing comes up is in making tables good for reporting. Consider the examples of keeping an accounting period (based on a transaction date already in the table) and a vendor's name.
The reporting people may want to report on the data by month. I have often included along integer (often called ?Period?). It combines the four digit year and the two-digit month (year times one hundred, plus month number). This construction is flexible enough to handle all of the accounting periods, and it fits nicely in most table indexes. Accounting periods, by definition, do not change unless the transaction date changes. Even that is rare.
Vendor names, on the other hand, change frequently. In addition to managing the initial entry, we would need to write code related to the vendor table to update this table. This would make a simple change to one text field into a long database update.
Bottom line, I de-normalized the accounting period, but did not de-normalize the vendor name. I made a data mart for theanalyst that needed a vendor name that close to the transaction table.
One such place de-normalizing comes up is in making tables good for reporting. Consider the examples of keeping an accounting period (based on a transaction date already in the table) and a vendor's name.
The reporting people may want to report on the data by month. I have often included along integer (often called ?Period?). It combines the four digit year and the two-digit month (year times one hundred, plus month number). This construction is flexible enough to handle all of the accounting periods, and it fits nicely in most table indexes. Accounting periods, by definition, do not change unless the transaction date changes. Even that is rare.
Vendor names, on the other hand, change frequently. In addition to managing the initial entry, we would need to write code related to the vendor table to update this table. This would make a simple change to one text field into a long database update.
Bottom line, I de-normalized the accounting period, but did not de-normalize the vendor name. I made a data mart for theanalyst that needed a vendor name that close to the transaction table.
If the software doesn't automatically add it, both the date and time the data was created and who created it should be added to every table.
Personally, I like to add the timestamp and user that last UPDATED the record. This makes it much easier to answer the age-old question of "Who changed my data?!"
Further to #1:
1) Use consistent prefixes and suffixes throughout your schema. For your internal key fields, end them ALL with ID, or Num, or Key or whatever. Just don't end one internal key field name with ID, another with Key, and another with Num. Similarly, don't start one phone number field with phn, another with tel and another with phone.
2) Whenever possible, if two tables are related by a common key field, name the fields exactly the same in the two tables. It drives me crazy when some DBA figures I can intuitively deduce that EmpID in one field is the key to the field UserNum in another table.
1) Use consistent prefixes and suffixes throughout your schema. For your internal key fields, end them ALL with ID, or Num, or Key or whatever. Just don't end one internal key field name with ID, another with Key, and another with Num. Similarly, don't start one phone number field with phn, another with tel and another with phone.
2) Whenever possible, if two tables are related by a common key field, name the fields exactly the same in the two tables. It drives me crazy when some DBA figures I can intuitively deduce that EmpID in one field is the key to the field UserNum in another table.
2) Whenever possible, if two tables are related by a common key field, name the fields exactly the same in the two tables. It drives me crazy when some DBA figures I can intuitively deduce that EmpID in one field is the key to the field UserNum in another table.
It drives me crazy when someone has given columns in different tables THE SAME NAME ... grrrr.
I take your point about totally incongruous names for these keys, and sure you can argue that aliases provide clarity on cases where joins are combined with groupings etc, but as a someone that usually has to clean up someone else's mess, this is one point of style that makes my brain ache when im trying to decipher old code.
If you have a foreign user key in your order table, call it PKUserID in tblUser and FKUserID in tblOrder. (and prefixing tables with tbl in Enterprise Manager helps group _your_ tables away from the annoyingly listed system tables ... and prefixing stored procs with stp too come to mention it ... but i digress ...)
I don't want bash any bibles, but these four points from the Art of Unix Programming (http://www.faqs.org/docs/artu/) sum up the feeling im trying to get here. (good solid sensibles guidelines when writing code of any type really ...)
Rule of Clarity: Clarity is better than cleverness.
Rule of Transparency: Design for visibility to make inspection and debugging easier.
Rule of Robustness: Robustness is the child of transparency and simplicity.
Rule of Extensibility: Design for the future, because it will be here sooner than you think.
But wait .. there's more (nod to Demtel .. im an aussie
... the more code you say you have to write, the more $$s you earn! And to top it all off, an ounce (or $1k) of prevention is better than a pound (or $20k) of cure!!I just hope people actually read this stuff ... kinda outta date, but ...
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
