|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Can you have one field containing multiple data?I’m developing a web site where you are able to advertise your summer house for subletting per week. Visitors are able to mark there interest by clicking on a certain week in the interface. This being the background I have trouble deciding on how to design the database. I have one table for Advertiser and I have one table for Interested. The question then is how to design the table called SubletSchedule. First out: Create Table SubletSchedule ( AdvertiserID, SubletYear, Week1, Week2, Week3, …and so on till Week53 ) -or- Create Table SubletSchedule ( AdvertiserID, SubletYear, SubletWeek, SubletStatus, InterestedID ) I see the trouble with the first solution would be that the Week-number columns would have to contain data for both week status and InterestedID. I’ve already worked with the first solution for two weeks but I realise now while writing this mail that it has faults. The question boils down to: Can you have one field containing multiple data. e.g. Week1 contains both status for that week and ID to the person who is interested in renting the summer house that week? Thanks in advance, Niclas Colleen You're jumping to table design before first understanding the logic behind
your problem. Based on your post there are three entities present (Lessor, Lessee and Property), and the relations between them: A lessor can advertise one or more properties. A lessee can express interest to lease one or more properties. A contract of lease is agreed upon between a lessee and a lessor regarding one or more properties (these are then no longer available to others, unless the contract is annulled). Other relations may exist - think about them. Now, step away from the keyboard, take a pen, a pad, go out, get some fresh air, and start drawing your database. Three or four versions later you'll be ready to create tables. ML Being a fast developer sometimes it has its down sides.
With the original specifications it wasn’t necessary to store data of the people interested in renting the summer house. It then seemed reasonable to build the SubletScehdule “on the length†letting each row contain all weeks and just putting the status of that week(available, booked, reserved) in column Week1, Week2, etc. Now since each week needs a reference to a the table Interested I’m afraid I’ll have to redo the table. It’s tempting to just slob in InterestedID after status in those same fields but that I would probably regret? Despite a tight schedule and a stressfull employer I’m probably better off following your advice to take five and look at it again. Cheers Show quote "ML" wrote: > You're jumping to table design before first understanding the logic behind > your problem. > > Based on your post there are three entities present (Lessor, Lessee and > Property), and the relations between them: > > A lessor can advertise one or more properties. > A lessee can express interest to lease one or more properties. > A contract of lease is agreed upon between a lessee and a lessor regarding > one or more properties (these are then no longer available to others, unless > the contract is annulled). > Other relations may exist - think about them. > > Now, step away from the keyboard, take a pen, a pad, go out, get some fresh > air, and start drawing your database. Three or four versions later you'll be > ready to create tables. > > > ML > It's tempting to just slob in InterestedID after I'm certain that you would regret that. It's a big mistake to have more> status in those same fields but that I would probably regret? than one column to store the same kind of information (like Week1, Week2, etc). Read a book (or at least an article) about database normalization. Razvan hi Niclas Colleen
I am not sure if i got the requirement right. But what i feel is u should not use so many columns asn its not a good database design. u can have something like: Create Table SubletSchedule ( AdvertiserID, SubletYear, WeekNumber, Status ) Please let me know if this solves the purpose. Or, is there anything missing. -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "niclascolleen" wrote: > Greetings all! > > I’m developing a web site where you are able to advertise your summer house > for subletting per week. Visitors are able to mark there interest by clicking > on a certain week in the interface. > > This being the background I have trouble deciding on how to design the > database. > I have one table for Advertiser and I have one table for Interested. > The question then is how to design the table called SubletSchedule. > > First out: > > Create Table SubletSchedule > ( > AdvertiserID, > SubletYear, > Week1, > Week2, > Week3, …and so on till > Week53 > ) > > -or- > > Create Table SubletSchedule > ( > AdvertiserID, > SubletYear, > SubletWeek, > SubletStatus, > InterestedID > ) > > I see the trouble with the first solution would be that the Week-number > columns would have to contain data for both week status and InterestedID. > > I’ve already worked with the first solution for two weeks but I realise now > while writing this mail that it has faults. > > > The question boils down to: > > Can you have one field containing multiple data. e.g. Week1 contains both > status for that week and ID to the person who is interested in renting the > summer house that week? > > > Thanks in advance, > Niclas Colleen > Thanks Chandra!
And you other guys too for responses! Yes, the alternative you present is the one I've been struggling with, but not sure if it'd be worth the effort redoing the code since I've been working on it for a couple of weeks already. But yes, the decision of rewriting the code at this early point now definately feels correct after the input here. Thanks again! Best regards, Niclas Colleen Show quote "Chandra" wrote: > hi Niclas Colleen > > I am not sure if i got the requirement right. But what i feel is u should > not use so many columns asn its not a good database design. u can have > something like: > > Create Table SubletSchedule > ( > AdvertiserID, > SubletYear, > WeekNumber, > Status > ) > > Please let me know if this solves the purpose. Or, is there anything missing. > > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "niclascolleen" wrote: > > > Greetings all! > > > > I’m developing a web site where you are able to advertise your summer house > > for subletting per week. Visitors are able to mark there interest by clicking > > on a certain week in the interface. > > > > This being the background I have trouble deciding on how to design the > > database. > > I have one table for Advertiser and I have one table for Interested. > > The question then is how to design the table called SubletSchedule. > > > > First out: > > > > Create Table SubletSchedule > > ( > > AdvertiserID, > > SubletYear, > > Week1, > > Week2, > > Week3, …and so on till > > Week53 > > ) > > > > -or- > > > > Create Table SubletSchedule > > ( > > AdvertiserID, > > SubletYear, > > SubletWeek, > > SubletStatus, > > InterestedID > > ) > > > > I see the trouble with the first solution would be that the Week-number > > columns would have to contain data for both week status and InterestedID. > > > > I’ve already worked with the first solution for two weeks but I realise now > > while writing this mail that it has faults. > > > > > > The question boils down to: > > > > Can you have one field containing multiple data. e.g. Week1 contains both > > status for that week and ID to the person who is interested in renting the > > summer house that week? > > > > > > Thanks in advance, > > Niclas Colleen > >  |
|||||||||||||||||||||||
Other interesting topics