Discussion Locked

Consider ratios and probabilities

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

In this week's SQL Server newsletter, author Arthur Fuller advises developers to consider the ratios and probabilities in database models.

Is this tip useful to you? What topics would you like us to cover in future editions of this newsletter? Please let us know.

If you aren't subscribed to the free SQL Server newsletter, click the following link to automatically sign up:
http://nl.com.com/MiniFormHandler?brand=builder&subs_channel=bldr_front_door&list_id=e046&tag=fb

4 replies

by tony_c_porritt

The article regarding ratios and probabilities in database models was dead right. I always find that there are no perfect solutions to problems if you're trying to solve them in the "real world". You sometimes have to go with what works best, rather than what is "technically correct".

by bob.monahon

First: I totally agree that one needs to know the frequency or distribution of key data characteristics.

Second, re: "you could include enough columns (such as IsEmployee) to flag the relationships any person has with the organization."

I've used a different approach: Model the relationship as a data column, named perhaps: person_type_cd [Person-Type Code]. I also set up a set of valid values (My DBA reference-implementation calls this a domain table). In your example, I might choose:
CU - customer
EM - employee
VN - vendor
BD - member of the board.

One immediate benefit: I can index on person_type_cd to resolve the implementation issue you identify with indexing multiple flag columns.

Another benefit: If (when) the business model grows or changes, I can add additional person_type_cd values without redesigning the database.

Another benefit: If (when) the data characteristics change (for instance, we acquire a company with 1,000 employees, 300 customers and 10 vendors) the database design is probably not impacted by this shift in the distribution of the data characteristics.

by chris

You state in the article that indexes would be useless on a column where the selection criteria would return less than one percent of the rows. But in reality, those are exactly the cases where b-tree indexes excel at data retrieval. Surely this was just a typo? Or a think-o?

Another solution is to have person_type codes (for example: 'P' for person, 'V' for vendor, and so on) and to have a table whose rows consist of (person_id, person_type) tuples. Then you would index on person_type so that you could still find the non-employee records quickly. This is better than simply putting the type code into the person table in those situations where persons can have more than one type (for example, if an employee can also be a customer).

Chris

by ehess

What if you include an index on CustomerId, EmployeeId, VendorId, MobId. Null values would not be included in the index, improving the search for a particular type of "Person"?

This discussion is closed to new replies.