Relationships

[david7777777777]

I've read up on relationships and pretty much understand the theory. Now I need a specific example to solidify my understanding. Here's what I have: SQL 2000 database that my IT department will use to track employees, inventory, what employee has what computer, etc. I'll stick to one small detail: the relationship between the computer table and the employee table. I'm building ASP pages as the front-end.

Here are the tables and their columns, and I'll re-build them if I have to, based on your advice. When I created these tables, I did not even know enough to be concerned with the precision and scale settings. Now those are greyed out when I look at the tables in Design view in Enterprise Manager. If I need to modify these properties in order to make the relationships work, please let me know how to do that. I'm very open to any design suggestions from you who have battle scars. The primary keys are auto-generated integer fields and the Indentity property is set to "Yes" for these columns in every table.

What I need is for you to let me know what I need to do in order to create the appropriate relationship between the Employee field in the tbl_desktops table and whatever field in the tbl_employees table that is appropriate. Thanks for your help.

tbl_employees
ID (Primary Key, integer)
LastName (varchar)
FirstName (varchar)
MiddleInitial (varchar)
Phone (varchar)

tbl_desktops
ID (Primary Key, integer)
AssetNumber (varchar)
Employee (varchar)
DataPort (varchar)
Manufacture (varchar)

 

[TheInsider]

I assume the "Employee" field in the "tbl_desktops" table is supposed to identify the employee that the PC belongs to. You probably want to rename this to "EmployeeID". Next this should be of the exact same type as the "ID" field in the "tbl_employees" table. Therefore, make the "EmployeeID" column an integer, but don't make it a primary key or incremental. You should index the foreign key ("EmployeeID&quot though. Then define your relationship between "tbl_Employees.ID" and "tbl_desktops.EmployeeID". Finally, don't use a varchar for the MiddleInitial field, as this creates useless overhead.

 

[david7777777777]

Good info, thanks. What is the most efficient data type to use for the MiddieInital field in your opinion? Also, what is the deal with indexing? What exactly does it do and is it something you should do to all tables/fields/databases or is it something you onhly need to do in certain scenarios? I'm going to read about it right now but I'd like to hear your opinion too.

 

[TheInsider]

You should use a char data type for the "MiddleInitial" field. This is because a middle initial field is generally 1 to 3 characters long (some people have a couple middle names). The varchar field is used to save space by only storing the actual characters entered, where as a char field always stores the same number of bytes for each field... padding the unused characters.

Two things you should know about the varchar field: it requires some overhead to tell it where the field ends and the next field begins. If your field only expects 1 or 2 characters, or 1 or 2 bytes (2 or 4 if unicode), the space required to store the varchar position info will outweigh what you could possibly save. Basically you end up storing more with varchar than with char.

The second thing is that it takes longer for the system to retrieve your data because the field is not a fixed length. I don't know the optimum point at which you choose a varchar over a char, however, if storage space is a real concern, I would start to use varchar for fields that are roughly 8-10 characters or more.

Indexes speed up your queries, just like the names at the top of the page in the phone book. You can open up the phone book to the middle if you want to find "McDonald" because the listings are in order and you will probably find "M" around the middle of the book. If the phone book was unsorted you would have to look through each row from front to back until you found the person you were looking for. This would be quite slow!

A good candidate for indexing are the fields that you place in the WHERE clause of your query... or the criteria fields.

Indexing can slow down data entry, however. This is another balancing act. Unfortunately, I am not the definitive expert on the subject, so I can only give you approximate answers. I recommend reading the books online for more accurate information.

 

[david7777777777]

No, I never expect anyone on here to read for me. I'm always looking for information that only real-world experience can provide. So now I'm thinking down the road a bit, specifically about how this relationship will affect my lookup lists when I make my ASP page that serves as the user interface for this database. What visible or tangible evidence will I see from establishing this relationship, or is it a tangible advantage at all?