Best Practices Question?

Have a question about which method is the more accepted method.
I have two tables: table1 and table2.
They are joined by a primary and foreign key field:
table1
table1ID (Primary Key)
Room
NotAvailable (bit field: 1 will be not available and 0 will be available)
table2
table2ID (Primary Key)
table1ID (Foreign Key to table1)
DateUsed
There will only be records in table2 for rooms that are not available, so
when the two tables are inner joined, no matter how many records there are i
n
table1, the only records that will show up is what is matched in table2.
Question is: Is it good practice to use a field such as NotAvailable, to kno
w
that a room is not available, or use the results of the join to set a
NotAvailable property field in my code?
Thank you for any responses.
Note: I cannot use our company's actual field names, so disregard what the
names are and other ways to show a room as not available. Just want to show
the structure for the question I am asking.The problem with the NotAvailable field is that it requires modification
whenever there is an INSERT or Update in another table. And then the
question arises: "What exactly does NotAvailable mean since there is no time
period included". Is it NotAvailable today, tomorrow, next week, etc. That
provides opportunities for de-synching of the data -unless there is a
Trigger on the Table2.
Using a query joining the two tables (perhaps including a calendar table for
future dates) 'should' always provide an accurate presentation of data. (I'm
thinking of tables for Rooms and Reservations -therefore the Calendar table
is needed.)
It's not a 'Best Practice' to store data that is the result of some
manipulation of other data. But with careful planning, sometimes it has to
be done.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:AA6DB4DC-1E7E-4007-AEDA-C8355D385A28@.microsoft.com...
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
> in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to
> know
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to
> show
> the structure for the question I am asking.|||Mike Collins wrote:
> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.
You should rely on the data in table2 to determine if a room is
available. Using the bit field, you're exposing yourself to potentially
out-of-sync data, and you're duplicating the room status.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank you to both of you for your replies. That is the way I was thinking it
should be just needed a better way to explain it and get my point across.
"Mike Collins" wrote:

> Have a question about which method is the more accepted method.
> I have two tables: table1 and table2.
> They are joined by a primary and foreign key field:
> table1
> table1ID (Primary Key)
> Room
> NotAvailable (bit field: 1 will be not available and 0 will be available)
> table2
> table2ID (Primary Key)
> table1ID (Foreign Key to table1)
> DateUsed
> There will only be records in table2 for rooms that are not available, so
> when the two tables are inner joined, no matter how many records there are
in
> table1, the only records that will show up is what is matched in table2.
> Question is: Is it good practice to use a field such as NotAvailable, to k
now
> that a room is not available, or use the results of the join to set a
> NotAvailable property field in my code?
> Thank you for any responses.
> Note: I cannot use our company's actual field names, so disregard what the
> names are and other ways to show a room as not available. Just want to sho
w
> the structure for the question I am asking.