Issue with db design - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Issue with db design

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-10-31 : 16:01:41

I thought I had this worked out correctly but I am having issues using them.

I have several tables but these two, FACILITY_TYPE and FACILITIES are the ones that I am having problem with. Below are the tables:

   FACILITY
    facilityCode
    facilitySizeMin /* minimum party size */
    facilitySizeMax /* maximum party size */
    facilityOpen /* What time facility is open */
    facilityClose /* What time facility is closed */
    facilityImageURL /* url where image will be stored */
    
   FACILITY_TYPE
    facility_Type_Id	int PK
    facility_Type	nvarchar(100) /* park, pavilion, ... */ 
    facility_Name	nvarchar(250)	
    facilitySize  /* small, medium, large, N/A */
    
    
    RESERVATIONS /* where user's selections are stored. I am okey with this table */

What we would like to do is present a form through our web app to users and present the data in one of two layouts.

One, either we present two dropdowns, one says, Please select Facility Type and after their selection, the second dropdown says, please select Facility details.

OR

The second option which says, Please select a facility. Once a selection is made, then the next dropdown asks them to select Facility Type.

Either option doesn't seem to be working with my current design.

Can someone please help me fix these two tables?

Thanks a bunch.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-31 : 16:17:41

HOw do you join Facility and Facility_Type? I don't see any obvious foreign key column

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-10-31 : 16:26:07

Thanks for the prompt response.

I didn't. I joined the facilityCode from FACILITIES table with Reservations and I did same for FACILITY_TYPE.

Reason I did so was because both tables, FACILITIES and FACILITY_TYPE are lookup tables and I could not figure out how to join the two given the fact that ownwers of this app will be prepopulating them.

Your question is a great one because that is the source of my confusion.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-31 : 16:55:00

I think you need a third table that will link these two tables together.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-11-02 : 12:01:04

Hi Tara,

Let me try to put my need in a different light:

Here is how I would like my page set up for a potential user:

Please select facility Name: (user will select from dropdown)

Based on selection, another dropdown with facility type:

Please select facility type: (user will select pavilion, etc from dropdown).

Then a user is asked to select facility size. This may or may not be have any relation to facilities or facility type tables.

Then of course there are other fields in facility table as shown in my earlier post.

I am not sure a third table between Facility table and facilityType table will solve this problem.

I am thinking in terms of formulating the query to go with the design and so far, none of suggestions seem to be working based on what I am planning to do with the dropdowns.

Many thanks for the assistance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-02 : 13:51:47

You need a way to join Facilities with FacilityTypes. Typically this would be done by including the primary key of the facility type (the facilitytypeid) in the facility table as a foreign key.

You see, in your application, once a user selects a facility name the facility type can be retrieved based on the FK relationship and no second drop-down is needed. Same thing with facility size etc.

The other way would be for the user to specify what he needs in terms of size and type, the populate the facility names from facilities that have those features. Again, you'll need a link from the Facility to the Facility Type tables to make this work.

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2014-11-02 : 19:47:48

hi again gbritton,

Maybe, I am not explaining well.

What you just described would work if user is just selecting an option and getting rest of data populated.

In a way, that will be happening but in our situation, a user will be using following as search parameters:

FacilityName, eg, Herndon Park

FacilityType, eg, Pavilion

Facility Size, eg, Small, Medium, Large

Date when facility is needed.

Based on these parameters, a user will either be told the facility is available or not available.

So,one of my greatest issue is that it is not possible to add foreign key to another lookup table.

Second, a lookup tabe should not have as many fieldnames as the Facility table I posted originally.

Tara is on track with the suggestion made but again, my concern that is if I should have a third table that will contain facilityCode and FacilityTypeId, how can the user be able to search by facilitySize?

If this can be resolved successfully, I think it will solve my problem.

I don't know if I need another table called facilitySize, another facilityDate, etc

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-03 : 07:04:44

Facility size is stored in the type tasble (seems wrong) so once you have the xref table, that's easy.

BTW Tara is right if you won't add a type I'd to the facility table. However that is not the best design unlessma facility can have more than one type. Can it?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources