Hi all have a bit of a design question with regards to a sql server database I am buliding for my site. I am new to database that have more than one table. I have a table that contains an establishments information example bellow: EstID EstName EstAdd EstPostc EstPhone EstEmail The problem I have is each establishment can have multiple facilities. I have a facilities table as bellow: FacilID FacilName this holds all the avalable facilities How do I then link these to the establishment details as an establishment can have many facilities but I want the list of facilities to be pre defined not user entered any idea please help lee
You can link the tables by putting an extra field on the Faclities table called EstID. Then you can link the tables by putting the relevant EstID in the EstID field in the facilities table. I'm not sure what you mean about the pre defined facilties.
Hi there I did think of that but then I tought the facilities name would have to keep being repeated as each establishment has multiple facilities and I want a table the contains the list of facilities that the user of the site can choice and add to there establishment, soory im a bit naff at explaining this I came up with establishment table EstID EstName EstAdd EstPostc EstPhone EstEmail linktable Esid Facilid facilities table FacilID FacilName is this plausable haveing a table in the middle to relate the selected facilities in the facilities table to the establishment table hope you can help lee
Yeah sure, I understood you. You are correct, that is what you need to do. Forgive me did not explain myself clearly. Create one table, facilities and have the following: facid facname In the facilities table add all the unqiue facilities, only have to enter each facilitiy once. Then you can have another table called estblishments. In this table put all the details you want to include about the establishment. EstID EstName EstAdd EstPostc EstPhone EstEmail Then, like you say have a middle table that acts as a go between. As you say: linkid Esid Facilid This process I always use and does work well. You may want to add another field to the links table called linkid, this can make it easier for you to delete associations later. Hope thats clearer.
