|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Field value determines whether there is extra info that should be held about that recordI would like to have your advice in a little DB design issue. I'll try to demonstrate it by an example: Guess I have DB to hold information about rooms that are in a house. Each record represent a room, and has many fields like: width, height, wall color etc. One of the fields in the 'rooms' table is 'SleepingOption', which has 3 optional values: 1. None 2. Floor. 3. Bed. If a room has a bed, it should hold extra information about that bed. In addidtion, There is a limit of 1 bed per room. The problem is where and how to hold the extra information about the bed. I could add the extra bed information fields into the 'room' table, but that will cause many records to have null values in the bed information fields, since most rooms do not have beds in them. On the other hand, splitting the beds information into seperate table makes things complicated, since I have a unique identifier for the 'rooms' table, and another unique identifier for the 'beds' table (I've tried to solve that by putting FK in the beds table, then by other solution tried to put the FK in the rooms table, and both options are complicated..). There is no need to hold information about beds without rooms, so I don't have to have a seperate table for beds. In addition, when a room is deleted, the bed which is in it should be deleted too. There actually shouldn't be an option to delete a BED, but if you wish, such an option should just change the 'SleepingOption' to 'None' or 'Floor' (The fact that there are 2 other options besides 'Bed' makes me think that it's better to delete a room and not a bed). Don't be worry about deleting a whole room information just to delete a bed. That's OK (since the 'real' DB is a bit different). How should I design that? Regards, Amir. I would:
1. on table rooms with PK(room_id), 1.1 add a column SleepingOption (let's assume that 3 means 'bed') 1.2 add a unique constraint on(room_id, SleepingOption) 2. On table beds 2.1 add a column SleepingOption with check constraint SleepingOption=3 2.2 have a PK on room_id 2.3 have a FK on (room_id, SleepingOption) referencing rooms(room_id, SleepingOption) Alexander Kuznetsov wrote:
> I would: 2.4 Add the ON DELETE CASCADE option to the foreign key.> 1. on table rooms with PK(room_id), > 1.1 add a column SleepingOption (let's assume that 3 means 'bed') > 1.2 add a unique constraint on(room_id, SleepingOption) > > 2. On table beds > 2.1 add a column SleepingOption with check constraint SleepingOption=3 > 2.2 have a PK on room_id > 2.3 have a FK on (room_id, SleepingOption) referencing rooms(room_id, > SleepingOption) -- David Portas SQL Server MVP -- > 2.4 Add the ON DELETE CASCADE option to the foreign key. I agree, because the OP's requirements include:"when a room is deleted, the bed which is in it should be deleted too. " Thanks You mentioned that:
> If a room has a bed, it should hold extra information about that bed. This implies a seperate table for beds, but how many bed related attributes (description, occupancy, etc.) are needed and would these values be room specific? Perhaps you just need a table called [Beds] that stores a few basic types of sleeping arrangements and the column BedID is a foreign key in the [Rooms] table. This type of modeled relationship is very common and simple. [Rooms] RoomID Height Width WallColor BedID 1 20 15 Gray 3 2 20 20 Green 1 3 10 5 Zebra Print Wallpaper 6 [Beds] BedID BedDesc SleepOccupancy 1 None 0 2 Floor 4 3 King Sized Bed 2 4 Queen Sized Bed 2 5 Single Sized Bed 1 6 Sofa Bed 1 Show quote "Amir" <ag***@actcom.co.il> wrote in message news:Obn$aNWDGHA.2320@TK2MSFTNGP11.phx.gbl... > Hi, > > I would like to have your advice in a little DB design issue. I'll try to > demonstrate it by an example: > > Guess I have DB to hold information about rooms that are in a house. Each > record represent a room, and has many fields like: width, height, wall > color etc. One of the fields in the 'rooms' table is 'SleepingOption', > which has 3 optional values: 1. None 2. Floor. 3. Bed. > If a room has a bed, it should hold extra information about that bed. In > addidtion, There is a limit of 1 bed per room. > > The problem is where and how to hold the extra information about the bed. > I could add the extra bed information fields into the 'room' table, but > that will cause many records to have null values in the bed information > fields, since most rooms do not have beds in them. > > On the other hand, splitting the beds information into seperate table > makes things complicated, since I have a unique identifier for the 'rooms' > table, and another unique identifier for the 'beds' table (I've tried to > solve that by putting FK in the beds table, then by other solution tried > to put the FK in the rooms table, and both options are complicated..). > There is no need to hold information about beds without rooms, so I don't > have to have a seperate table for beds. > In addition, when a room is deleted, the bed which is in it should be > deleted too. There actually shouldn't be an option to delete a BED, but if > you wish, such an option should just change the 'SleepingOption' to 'None' > or 'Floor' (The fact that there are 2 other options besides 'Bed' makes me > think that it's better to delete a room and not a bed). Don't be worry > about deleting a whole room information just to delete a bed. That's OK > (since the 'real' DB is a bit different). > > How should I design that? > > Regards, > Amir. > Also, in regard to this specific model, since Beds contains only types of
sleeping arrangements and not room specific detail, you should not delete the related Bed when a Room is deleted, so there is no need for a cascading delete constraint. Show quote "JT" <some***@microsoft.com> wrote in message news:OclnZYXDGHA.2320@TK2MSFTNGP12.phx.gbl... > You mentioned that: >> If a room has a bed, it should hold extra information about that bed. > > This implies a seperate table for beds, but how many bed related > attributes (description, occupancy, etc.) are needed and would these > values be room specific? > > Perhaps you just need a table called [Beds] that stores a few basic types > of sleeping arrangements and the column BedID is a foreign key in the > [Rooms] table. This type of modeled relationship is very common and > simple. > > [Rooms] > RoomID Height Width WallColor BedID > 1 20 15 Gray 3 > 2 20 20 Green 1 > 3 10 5 Zebra Print Wallpaper 6 > > [Beds] > BedID BedDesc SleepOccupancy > 1 None 0 > 2 Floor 4 > 3 King Sized Bed 2 > 4 Queen Sized Bed 2 > 5 Single Sized Bed 1 > 6 Sofa Bed 1 > > > "Amir" <ag***@actcom.co.il> wrote in message > news:Obn$aNWDGHA.2320@TK2MSFTNGP11.phx.gbl... >> Hi, >> >> I would like to have your advice in a little DB design issue. I'll try to >> demonstrate it by an example: >> >> Guess I have DB to hold information about rooms that are in a house. Each >> record represent a room, and has many fields like: width, height, wall >> color etc. One of the fields in the 'rooms' table is 'SleepingOption', >> which has 3 optional values: 1. None 2. Floor. 3. Bed. >> If a room has a bed, it should hold extra information about that bed. In >> addidtion, There is a limit of 1 bed per room. >> >> The problem is where and how to hold the extra information about the bed. >> I could add the extra bed information fields into the 'room' table, but >> that will cause many records to have null values in the bed information >> fields, since most rooms do not have beds in them. >> >> On the other hand, splitting the beds information into seperate table >> makes things complicated, since I have a unique identifier for the >> 'rooms' table, and another unique identifier for the 'beds' table (I've >> tried to solve that by putting FK in the beds table, then by other >> solution tried to put the FK in the rooms table, and both options are >> complicated..). >> There is no need to hold information about beds without rooms, so I don't >> have to have a seperate table for beds. >> In addition, when a room is deleted, the bed which is in it should be >> deleted too. There actually shouldn't be an option to delete a BED, but >> if you wish, such an option should just change the 'SleepingOption' to >> 'None' or 'Floor' (The fact that there are 2 other options besides 'Bed' >> makes me think that it's better to delete a room and not a bed). Don't be >> worry about deleting a whole room information just to delete a bed. >> That's OK (since the 'real' DB is a bit different). >> >> How should I design that? >> >> Regards, >> Amir. >> > > Amir wrote:
Show quote > Hi, You could do something like the following. If this doesn't suit you,> > I would like to have your advice in a little DB design issue. I'll try to > demonstrate it by an example: > > Guess I have DB to hold information about rooms that are in a house. Each > record represent a room, and has many fields like: width, height, wall color > etc. One of the fields in the 'rooms' table is 'SleepingOption', which has 3 > optional values: 1. None 2. Floor. 3. Bed. > If a room has a bed, it should hold extra information about that bed. In > addidtion, There is a limit of 1 bed per room. > > The problem is where and how to hold the extra information about the bed. I > could add the extra bed information fields into the 'room' table, but that > will cause many records to have null values in the bed information fields, > since most rooms do not have beds in them. > > On the other hand, splitting the beds information into seperate table makes > things complicated, since I have a unique identifier for the 'rooms' table, > and another unique identifier for the 'beds' table (I've tried to solve that > by putting FK in the beds table, then by other solution tried to put the FK > in the rooms table, and both options are complicated..). > There is no need to hold information about beds without rooms, so I don't > have to have a seperate table for beds. > In addition, when a room is deleted, the bed which is in it should be > deleted too. There actually shouldn't be an option to delete a BED, but if > you wish, such an option should just change the 'SleepingOption' to 'None' > or 'Floor' (The fact that there are 2 other options besides 'Bed' makes me > think that it's better to delete a room and not a bed). Don't be worry about > deleting a whole room information just to delete a bed. That's OK (since the > 'real' DB is a bit different). > > How should I design that? > > Regards, > Amir. please explain why you think that using a foreign key is "complicated"? Don't you use foreign keys at all (!)? CREATE TABLE rooms (room_no INTEGER NOT NULL PRIMARY KEY, sleeping_option INTEGER NOT NULL CHECK (sleeping_option BETWEEN 1 AND 3), UNIQUE (room_no, sleeping_option) /* ... other columns for Rooms */) ; CREATE TABLE beds (room_no INTEGER NOT NULL PRIMARY KEY, sleeping_option INTEGER NOT NULL DEFAULT (3) CHECK (sleeping_option=3), FOREIGN KEY (room_no, sleeping_option) REFERENCES rooms (room_no, sleeping_option) ON DELETE CASCADE /* ... other columns for Beds */) ; -- David Portas SQL Server MVP -- Hi,
Thank you all for the answers. I was worried about the fact that this example will take things a bit more complicated then it is. I'm starting to realize that maybe that example wasn't a very clever one, so I'll tell you the 'real' situation (it's not that complicated when I think about it now..): I'm trying to build an Access project which will be used to make documentation about 'events' that are happening. Imagine that a person writes every event that happens to him, e.g.: I wake up, I'm watching TV, I go to work, etc. The problem is that sometimes there is extra information which is needed to be held about specific events. For example, if a person is talking to someone (a 'talking' event), then there should be extra information that needs to be held about 'How did they make the talking', for example: face to face, by phone, by Email, by video conference etc. Now the problem is that most of the 'events' are not 'taking to other people' kind of events (means: most of the events do not require holding extra information besides the information which is in the main table), so there is kind of a problem holding that extra information in the same main table with all the rest of the events (This will cause many null fields). Now you can understand why if someone is deleteing the main event (room), the extra information (bed) is also deleted (Now you might understand my poor choice of the example.. sorry). About the solutions you mentioned: 1. I think that using two field primary key might cause problems since I use ADP, so wouldn't that cause probloem with the Resync Command? In addition, I think it's not necessary. 2. The main issue I don't know how to solve is that I don't know which option from the 3 following options is the better to choose: a. Put all the information in the same table (Good: Simplicity. Bad: Lots of nulls). b. Have 2 tables with 1 primary key field for each one, and a field in the extra information table which is a lookup to the main events table. c. Have 2 tables with 1 primary key field for each one, and a field in the main events table which is a lookup to the extra information table. As to your question about the complexity of using foreign keys, I do use foreign keys, but I'm having a difficulty here because I don't know which table should have the primary key and which one the foreign key? Logically, the B option seems the 'correct' to me, since it implies that if I delete something from the main table, it will also delete the relevent record from the extra information table (assuming I use cascade delete). Am I right (I mean, is that the B or the C one?)? (BTW, in such a case (B) which one of the fields in the main table and the extra information table is called the PK and which one the FK? I'm confused because I'm used to saying 'a field in table A which is a lookup to table B' - the FK is in A or in B?) Please accept my apologize about the previous example.. :) I appreciate your help very much, Kind Regards, Amir. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1135969795.547086.174150@g49g2000cwa.googlegroups.com... > Amir wrote: >> Hi, >> >> I would like to have your advice in a little DB design issue. I'll try to >> demonstrate it by an example: >> >> Guess I have DB to hold information about rooms that are in a house. Each >> record represent a room, and has many fields like: width, height, wall >> color >> etc. One of the fields in the 'rooms' table is 'SleepingOption', which >> has 3 >> optional values: 1. None 2. Floor. 3. Bed. >> If a room has a bed, it should hold extra information about that bed. In >> addidtion, There is a limit of 1 bed per room. >> >> The problem is where and how to hold the extra information about the bed. >> I >> could add the extra bed information fields into the 'room' table, but >> that >> will cause many records to have null values in the bed information >> fields, >> since most rooms do not have beds in them. >> >> On the other hand, splitting the beds information into seperate table >> makes >> things complicated, since I have a unique identifier for the 'rooms' >> table, >> and another unique identifier for the 'beds' table (I've tried to solve >> that >> by putting FK in the beds table, then by other solution tried to put the >> FK >> in the rooms table, and both options are complicated..). >> There is no need to hold information about beds without rooms, so I don't >> have to have a seperate table for beds. >> In addition, when a room is deleted, the bed which is in it should be >> deleted too. There actually shouldn't be an option to delete a BED, but >> if >> you wish, such an option should just change the 'SleepingOption' to >> 'None' >> or 'Floor' (The fact that there are 2 other options besides 'Bed' makes >> me >> think that it's better to delete a room and not a bed). Don't be worry >> about >> deleting a whole room information just to delete a bed. That's OK (since >> the >> 'real' DB is a bit different). >> >> How should I design that? >> >> Regards, >> Amir. > > You could do something like the following. If this doesn't suit you, > please explain why you think that using a foreign key is "complicated"? > Don't you use foreign keys at all (!)? > > CREATE TABLE rooms (room_no INTEGER NOT NULL PRIMARY KEY, > sleeping_option INTEGER NOT NULL CHECK (sleeping_option BETWEEN 1 AND > 3), UNIQUE (room_no, sleeping_option) /* ... other columns for Rooms > */) ; > > CREATE TABLE beds (room_no INTEGER NOT NULL PRIMARY KEY, > sleeping_option INTEGER NOT NULL DEFAULT (3) CHECK (sleeping_option=3), > FOREIGN KEY (room_no, sleeping_option) REFERENCES rooms (room_no, > sleeping_option) ON DELETE CASCADE /* ... other columns for Beds */) ; > > -- > David Portas > SQL Server MVP > -- > Amir wrote:
Show quote > Hi, Much easier and clearer to describe things with CREATE TABLE statements> > Thank you all for the answers. I was worried about the fact that this > example will take things a bit more complicated then it is. I'm starting to > realize that maybe that example wasn't a very clever one, so I'll tell you > the 'real' situation (it's not that complicated when I think about it > now..): > > I'm trying to build an Access project which will be used to make > documentation about 'events' that are happening. Imagine that a person > writes every event that happens to him, e.g.: I wake up, I'm watching TV, I > go to work, etc. The problem is that sometimes there is extra information > which is needed to be held about specific events. For example, if a person > is talking to someone (a 'talking' event), then there should be extra > information that needs to be held about 'How did they make the talking', for > example: face to face, by phone, by Email, by video conference etc. > Now the problem is that most of the 'events' are not 'taking to other > people' kind of events (means: most of the events do not require holding > extra information besides the information which is in the main table), so > there is kind of a problem holding that extra information in the same main > table with all the rest of the events (This will cause many null fields). > Now you can understand why if someone is deleteing the main event (room), > the extra information (bed) is also deleted (Now you might understand my > poor choice of the example.. sorry). > > About the solutions you mentioned: > 1. I think that using two field primary key might cause problems since I use > ADP, so wouldn't that cause probloem with the Resync Command? In addition, I > think it's not necessary. > 2. The main issue I don't know how to solve is that I don't know which > option from the 3 following options is the better to choose: > a. Put all the information in the same table (Good: Simplicity. Bad: Lots of > nulls). > b. Have 2 tables with 1 primary key field for each one, and a field in the > extra information table which is a lookup to the main events table. > c. Have 2 tables with 1 primary key field for each one, and a field in the > main events table which is a lookup to the extra information table. > > As to your question about the complexity of using foreign keys, I do use > foreign keys, but I'm having a difficulty here because I don't know which > table should have the primary key and which one the foreign key? > > Logically, the B option seems the 'correct' to me, since it implies that if > I delete something from the main table, it will also delete the relevent > record from the extra information table (assuming I use cascade delete). Am > I right (I mean, is that the B or the C one?)? (BTW, in such a case (B) > which one of the fields in the main table and the extra information table is > called the PK and which one the FK? I'm confused because I'm used to saying > 'a field in table A which is a lookup to table B' - the FK is in A or in B?) > > Please accept my apologize about the previous example.. :) > > I appreciate your help very much, > Kind Regards, > Amir. > than with a long narrative so here's a revised example. CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many different types you have */), UNIQUE (event_no, event_type) /* ... other columns for events */) ; CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY, event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON DELETE CASCADE /* ... other columns for event type 1 */) ; CREATE TABLE type2_events (event_no INTEGER NOT NULL PRIMARY KEY, event_type INTEGER NOT NULL DEFAULT (2) CHECK (event_type=2), FOREIGN KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON DELETE CASCADE /* ... other columns for event type 2 */) ; .... and so on for as many types of event as you will need. Notice that the compound key in the Events table and the CHECK constraints in the other tables serve an important purpose. They ensure that events of each type can only appear in the correct table and nowehere else. > 1. I think that using two field primary key might cause problems since I use The primary keys in my example are all single columns. I really don't> ADP, so wouldn't that cause probloem with the Resync Command? know if a compound foreign key causes any problems in ADP. Ask about that in an Access forum. If it does, then I'd look for a solution to those problems at the client side. It doesn't make sense to me to compromise your data model and integrity for the sake of some quirky behaviour of Access. -- David Portas SQL Server MVP -- Hi,
Why do I need the event_type field in the type1_events and type2_events tables? These tables are already 'linked' to the events table by the event_no field, so that I can join the event table with type1_event or type2_event without it, can't I? Thanks for your help, Regards, Amir. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1136039574.292689.186680@g47g2000cwa.googlegroups.com... > Amir wrote: >> Hi, >> >> Thank you all for the answers. I was worried about the fact that this >> example will take things a bit more complicated then it is. I'm starting >> to >> realize that maybe that example wasn't a very clever one, so I'll tell >> you >> the 'real' situation (it's not that complicated when I think about it >> now..): >> >> I'm trying to build an Access project which will be used to make >> documentation about 'events' that are happening. Imagine that a person >> writes every event that happens to him, e.g.: I wake up, I'm watching TV, >> I >> go to work, etc. The problem is that sometimes there is extra information >> which is needed to be held about specific events. For example, if a >> person >> is talking to someone (a 'talking' event), then there should be extra >> information that needs to be held about 'How did they make the talking', >> for >> example: face to face, by phone, by Email, by video conference etc. >> Now the problem is that most of the 'events' are not 'taking to other >> people' kind of events (means: most of the events do not require holding >> extra information besides the information which is in the main table), so >> there is kind of a problem holding that extra information in the same >> main >> table with all the rest of the events (This will cause many null fields). >> Now you can understand why if someone is deleteing the main event (room), >> the extra information (bed) is also deleted (Now you might understand my >> poor choice of the example.. sorry). >> >> About the solutions you mentioned: >> 1. I think that using two field primary key might cause problems since I >> use >> ADP, so wouldn't that cause probloem with the Resync Command? In >> addition, I >> think it's not necessary. >> 2. The main issue I don't know how to solve is that I don't know which >> option from the 3 following options is the better to choose: >> a. Put all the information in the same table (Good: Simplicity. Bad: Lots >> of >> nulls). >> b. Have 2 tables with 1 primary key field for each one, and a field in >> the >> extra information table which is a lookup to the main events table. >> c. Have 2 tables with 1 primary key field for each one, and a field in >> the >> main events table which is a lookup to the extra information table. >> >> As to your question about the complexity of using foreign keys, I do use >> foreign keys, but I'm having a difficulty here because I don't know which >> table should have the primary key and which one the foreign key? >> >> Logically, the B option seems the 'correct' to me, since it implies that >> if >> I delete something from the main table, it will also delete the relevent >> record from the extra information table (assuming I use cascade delete). >> Am >> I right (I mean, is that the B or the C one?)? (BTW, in such a case (B) >> which one of the fields in the main table and the extra information table >> is >> called the PK and which one the FK? I'm confused because I'm used to >> saying >> 'a field in table A which is a lookup to table B' - the FK is in A or in >> B?) >> >> Please accept my apologize about the previous example.. :) >> >> I appreciate your help very much, >> Kind Regards, >> Amir. >> > > Much easier and clearer to describe things with CREATE TABLE statements > than with a long narrative so here's a revised example. > > CREATE TABLE events (event_no INTEGER NOT NULL PRIMARY KEY, event_type > INTEGER NOT NULL CHECK (event_type BETWEEN 1 AND 10 /* however many > different types you have */), UNIQUE (event_no, event_type) /* ... > other columns for events */) ; > > CREATE TABLE type1_events (event_no INTEGER NOT NULL PRIMARY KEY, > event_type INTEGER NOT NULL DEFAULT (1) CHECK (event_type=1), FOREIGN > KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON > DELETE CASCADE /* ... other columns for event type 1 */) ; > > CREATE TABLE type2_events (event_no INTEGER NOT NULL PRIMARY KEY, > event_type INTEGER NOT NULL DEFAULT (2) CHECK (event_type=2), FOREIGN > KEY (event_no, event_type) REFERENCES events (event_no, event_type) ON > DELETE CASCADE /* ... other columns for event type 2 */) ; > > ... and so on for as many types of event as you will need. > > Notice that the compound key in the Events table and the CHECK > constraints in the other tables serve an important purpose. They ensure > that events of each type can only appear in the correct table and > nowehere else. > >> 1. I think that using two field primary key might cause problems since I >> use >> ADP, so wouldn't that cause probloem with the Resync Command? > > The primary keys in my example are all single columns. I really don't > know if a compound foreign key causes any problems in ADP. Ask about > that in an Access forum. If it does, then I'd look for a solution to > those problems at the client side. It doesn't make sense to me to > compromise your data model and integrity for the sake of some quirky > behaviour of Access. > > -- > David Portas > SQL Server MVP > -- > Amir wrote:
> Hi, Yes you can. And in your application you certainly would join on> > Why do I need the event_type field in the type1_events and type2_events > tables? > These tables are already 'linked' to the events table by the event_no field, > so that I can join the event table with type1_event or type2_event without > it, can't I? > event_no only. However, without the foreign key on event_type as well as event_no it would be possible for the same event to appear in multiple event type tables. That would be a serious anomaly because only one type is permitted for each event (that's my interpretation anyway). The compound foreign key constraint prevents that anomaly. -- David Portas SQL Server MVP -- Is having the event_type field in the type1_events and type2_events tables
required only if there is more than 1 event_type that requires storing of extra information about each event? I mean, if the rest of the event types except for 1 'special' one (the one which requires storing extra data) do not require storing any extra information about each event, in such a case can I ommit the event_type from the type1_events and type2_events tables? Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1136048604.305176.212980@o13g2000cwo.googlegroups.com... > Amir wrote: >> Hi, >> >> Why do I need the event_type field in the type1_events and type2_events >> tables? >> These tables are already 'linked' to the events table by the event_no >> field, >> so that I can join the event table with type1_event or type2_event >> without >> it, can't I? >> > > Yes you can. And in your application you certainly would join on > event_no only. However, without the foreign key on event_type as well > as event_no it would be possible for the same event to appear in > multiple event type tables. That would be a serious anomaly because > only one type is permitted for each event (that's my interpretation > anyway). The compound foreign key constraint prevents that anomaly. > > -- > David Portas > SQL Server MVP > -- > Amir wrote:
> Is having the event_type field in the type1_events and type2_events tables Suppose that event_type 1 was the only "special" type and suppose that> required only if there is more than 1 event_type that requires storing of > extra information about each event? > I mean, if the rest of the event types except for 1 'special' one (the one > which requires storing extra data) do not require storing any extra > information about each event, in such a case can I ommit the event_type from > the type1_events and type2_events tables? the only tables in your database were events and type1_events. Without the foreign key on event_type you could still have a situation where an event shown in the events table as type 2 had an invalid row in type1_events. That would leave your database in an inconsistent state. Example: INSERT INTO events (event_no, event_type) VALUES (1,2) ; INSERT INTO type1_events (event_no, event_type) VALUES (1,1 /* Mistake! This row should not exist! */) ; The foreign key prevents that from happening. Notice that the database would still be inconsistent if you remove the event_type column from the type1_events table because you would still have a row that shouldn't exist and that would contradict your events data. Now, you may believe that your application is smart enough to prevent mistakes like that from occurring but the principle and the art of effective database design is that data integrity rules are enforced wherever possible in the database, not in the application. One day, other applications will need to make use of this data. If you leave important business rules out of the database you'll have to make sure that every application and every consumer of the data is aware what those rules should be. Those users may then have to take special measures or write extra code to eliminate any errors that exist due to incosistent data. Those kinds of mistakes can be very expensive to rectify after the event. That's why the constraints are important and why good database designers are so careful about getting them right. Hope this helps. -- David Portas SQL Server MVP -- I think I understand the logic behind your words. Now I'll try to implement
that. Thank you very much for the detailed explanation! Kind Regards, Amir. Show quote "David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message news:1136051258.328069.89760@g14g2000cwa.googlegroups.com... > Amir wrote: >> Is having the event_type field in the type1_events and type2_events >> tables >> required only if there is more than 1 event_type that requires storing of >> extra information about each event? >> I mean, if the rest of the event types except for 1 'special' one (the >> one >> which requires storing extra data) do not require storing any extra >> information about each event, in such a case can I ommit the event_type >> from >> the type1_events and type2_events tables? > > Suppose that event_type 1 was the only "special" type and suppose that > the only tables in your database were events and type1_events. Without > the foreign key on event_type you could still have a situation where an > event shown in the events table as type 2 had an invalid row in > type1_events. That would leave your database in an inconsistent state. > Example: > > INSERT INTO events (event_no, event_type) VALUES (1,2) ; > > INSERT INTO type1_events (event_no, event_type) > VALUES (1,1 /* Mistake! This row should not exist! */) ; > > The foreign key prevents that from happening. Notice that the database > would still be inconsistent if you remove the event_type column from > the type1_events table because you would still have a row that > shouldn't exist and that would contradict your events data. > > Now, you may believe that your application is smart enough to prevent > mistakes like that from occurring but the principle and the art of > effective database design is that data integrity rules are enforced > wherever possible in the database, not in the application. One day, > other applications will need to make use of this data. If you leave > important business rules out of the database you'll have to make sure > that every application and every consumer of the data is aware what > those rules should be. Those users may then have to take special > measures or write extra code to eliminate any errors that exist due to > incosistent data. Those kinds of mistakes can be very expensive to > rectify after the event. That's why the constraints are important and > why good database designers are so careful about getting them right. > > Hope this helps. > > -- > David Portas > SQL Server MVP > -- > assuming that the room-bed analogy is actually a good one for your
'real' db... i don't believe it that "when a room is deleted, the bed which is in it should be deleted too". is a bed not an entity itself? deleting the bed when a room is deleted sounds like a deletion anomaly to me. are there intrinsic attributes to a bed, regardless of it being related to a room? [e.g., dimensions, type (king,bunk,etc.)] are there also attributes of the relationship to the room? [e.g., position in the room] i'd have a linking table between room and bed, and an instead of update trigger on the room table, to handle sleeping option changes e.g., -- set to text output, for readability set nocount on use tempdb go create table Room ( RoomID int not null unique, RoomName varchar(20) not null primary key, SleepingOptions int not null check (SleepingOptions in (1,2,3)), unique (RoomID, SleepingOptions) ) create table Bed ( BedID int not null primary key, BedType char(1) not null check (BedType in ('S','D','K','Q','B')) ) create table Bedroom ( RoomID int not null, SleepingOptions int not null check (SleepingOptions=3), BedID int not null references Bed (BedID), Position varchar(20) not null, foreign key (RoomID, SleepingOptions) references Room (RoomID, SleepingOptions) on delete cascade, unique (BedID) ) go create trigger detach_bedroom on room instead of update as begin set nocount on if update(sleepingoptions) begin delete from bedroom where exists (select * from inserted i join deleted d on i.roomid=d.roomid where d.roomid=bedroom.roomid and d.sleepingoptions=3 and i.sleepingoptions<>3 ) -- print for example only print 'deleted '+convert(varchar,@@rowcount) -- finish the update on room update room set RoomName=i.RoomName, SleepingOptions=i.SleepingOptions from Room join inserted i on Room.RoomID=i.RoomID join deleted d on i.roomid=d.roomid and i.SleepingOptions<>d.SleepingOptions -- print for example only print 'updated '+convert(varchar,@@rowcount) end end go insert into Room select 1, 'Room 1', 1 union all select 2, 'Room 2', 2 union all select 3, 'Room 3', 3 union all select 4, 'Room 4', 3 insert into Bed select 1, 'K' union all select 2, 'Q' union all select 3, 'S' insert into Bedroom select 3, 3, 1, 'middle n. wall' union all select 4, 3, 3, 'sw corner, w wall' print '*** Initial values ***' select * from Room select * from Bed select * from Bedroom update room set sleepingoptions=2 where RoomID in (1,3) -- remove link, not bed print '*** After updating SleepingOptions ***' select * from Room select * from Bedroom delete from Room where RoomID=4 -- remove link, not bed print '*** After deleting room ***' select * from Room select * from Bed select * from Bedroom drop table Bedroom drop table Bed drop table Room Amir wrote: Show quote > Hi, > > I would like to have your advice in a little DB design issue. I'll try to > demonstrate it by an example: > > Guess I have DB to hold information about rooms that are in a house. Each > record represent a room, and has many fields like: width, height, wall color > etc. One of the fields in the 'rooms' table is 'SleepingOption', which has 3 > optional values: 1. None 2. Floor. 3. Bed. > If a room has a bed, it should hold extra information about that bed. In > addidtion, There is a limit of 1 bed per room. > > The problem is where and how to hold the extra information about the bed. I > could add the extra bed information fields into the 'room' table, but that > will cause many records to have null values in the bed information fields, > since most rooms do not have beds in them. > > On the other hand, splitting the beds information into seperate table makes > things complicated, since I have a unique identifier for the 'rooms' table, > and another unique identifier for the 'beds' table (I've tried to solve that > by putting FK in the beds table, then by other solution tried to put the FK > in the rooms table, and both options are complicated..). > There is no need to hold information about beds without rooms, so I don't > have to have a seperate table for beds. > In addition, when a room is deleted, the bed which is in it should be > deleted too. There actually shouldn't be an option to delete a BED, but if > you wish, such an option should just change the 'SleepingOption' to 'None' > or 'Floor' (The fact that there are 2 other options besides 'Bed' makes me > think that it's better to delete a room and not a bed). Don't be worry about > deleting a whole room information just to delete a bed. That's OK (since the > 'real' DB is a bit different). > > How should I design that? > > Regards, > Amir. > > >> don't believe it that "when a room is deleted, the bed which is in it should be deleted too". is a bed not an entity itself? deleting the bedwhen a room is deleted sounds like a deletion anomaly to me. << Well, I do believe - I've seen similar situations in real life. For instance, some traffic accidents involve HazMats (Hazardous Materials). Lots of additional paperwork is required for those accidents. In due time all the records for the accidents are gone. All the additional HazMat-related paperwork is gone at the same time - it is not an independent entity, it can exists only when relted to a traffic accident. as i said, that was assuming that a real room-to-bed relationship was a
good analogy from the OP, I got the impression that the "bed" was a separate entity. in your example, the hazmat paperwork is the relationship between an accident and a hazmat. when that accident-hazmat relationship is gone, you wouldn't delete the material from your hazmat list. of course, that impression comes from years of listening to end users give vague explanations of what they want, and my experience in helping them get more specific :) Alexander Kuznetsov wrote: Show quote >>> don't believe it that "when a room is deleted, the bed which is in it > > should be deleted too". is a bed not an entity itself? deleting the bed > > when a room is deleted sounds like a deletion anomaly to me. > << > > Well, I do believe - I've seen similar situations in real life. > For instance, some traffic accidents involve HazMats (Hazardous > Materials). Lots of additional paperwork is required for those > accidents. In due time all the records for the accidents are gone. All > the additional HazMat-related paperwork is gone at the same time - it > is not an independent entity, it can exists only when relted to a > traffic accident. >  |
|||||||||||||||||||||||
Other interesting topics