John wrote:
> Hi,
>
> I am designing a table for classrooms with many features. Following
is
> the detail of the table (let's say the name of table is CRoom):
>
> 1. Room Name (key)
> 2. Building name (foreign key)
> 3. Capacity
> 4. Chairs
> 5. T-arm chairs
> 6. Tables
> 7. Desks
> 8. Lectern
> 9. 35 mm slide projector
> 10. Dual 35 mm slide projector
> 11. 3/4" video player
> 12. 1/2" video player
> 13. Chalkboard
> 14. Markerboard
> .....
>
> My questions are:
> a. Should I put everything in the same table?
> or
> b. If there is chairs in a classroom then there will be no T-arm
> chairs there,
> vice versa. So should I create a sepearte table for chairs and
> t-arm chairs
> and use the key for this as a foreign key in the CRoom table?
> c. For number 11 and 12, some classrooms have only 3/4" video player,
> some
> classrooms have only 1/2" video player and some classrooms have
> both.
> Should I create another table for them and set value 1 for 3/4"
> video player
> 2 for 1/2" video player and 3 for both? Then use the value 1, 2, 3
> in the
> CRoom table to represent the three differnt situations. If this is
> not
> proper, then how should I deal with this one?
>
> Thanks a lot in advance.


Try creating a table for "Items" that a classroom might have (3/4"
video player, chairs, whatever) and then you need another table to link
your classroom table and your items table, which will contain the
primary key for classroom and for item.

"Invisible" <alex.campos@gmail.com> wrote in message news:<cd7fja$mkb@odbk17.prod.google.com>...
> John wrote:
> > Hi,
> >
> > I am designing a table for classrooms with many features. Following
> is
> > the detail of the table (let's say the name of table is CRoom):
> >
> > 1. Room Name (key)
> > 2. Building name (foreign key)
> > 3. Capacity
> > 4. Chairs
> > 5. T-arm chairs
> > 6. Tables
> > 7. Desks
> > 8. Lectern
> > 9. 35 mm slide projector
> > 10. Dual 35 mm slide projector
> > 11. 3/4" video player
> > 12. 1/2" video player
> > 13. Chalkboard
> > 14. Markerboard
> > .....
> >
> > My questions are:
> > a. Should I put everything in the same table?
> > or
> > b. If there is chairs in a classroom then there will be no T-arm
> > chairs there,
> > vice versa. So should I create a sepearte table for chairs and
> > t-arm chairs
> > and use the key for this as a foreign key in the CRoom table?
> > c. For number 11 and 12, some classrooms have only 3/4" video player,
> > some
> > classrooms have only 1/2" video player and some classrooms have
> > both.
> > Should I create another table for them and set value 1 for 3/4"
> > video player
> > 2 for 1/2" video player and 3 for both? Then use the value 1, 2, 3
> > in the
> > CRoom table to represent the three differnt situations. If this is
> > not
> > proper, then how should I deal with this one?
> >
> > Thanks a lot in advance.
>
>
> Try creating a table for "Items" that a classroom might have (3/4"
> video player, chairs, whatever) and then you need another table to link
> your classroom table and your items table, which will contain the
> primary key for classroom and for item.


Thank you for your reply.
But I am still pretty confused here. I can create a seperate table for
items, but after that how should I deal with the situations like
question b and c?
Thanks again.

Here's a (partial) suggestion for the table structure.

CREATE TABLE Rooms (room_name VARCHAR(20) PRIMARY KEY, building_name
VARCHAR(20) REFERENCES Buildings (building_name), capacity INTEGER NOT NULL)

CREATE TABLE RoomEquipment (room_name VARCHAR(20) NOT NULL REFERENCES Rooms
(room_name), item_name VARCHAR(20) NOT NULL REFERENCES Equipment
(item_name), quantity INTEGER NOT NULL CHECK (quantity>0), PRIMARY KEY
(room_name,item_name))

--
David Portas
SQL Server MVP
--