1

I'm extremely green at database design and have been given a project to complete as part of an assignment.

My question is, in regards to values from a table, in this instance, it relates to health club facilities.

Let's say there are multiple facilities that a health club could have such as 24-hours gym, swimming pool, sauna, kids playroom, outdoor training park, cycle studio. My plan would be to store these and have a sequential ID number relating to each.

If I store this information in one table, can multiple ID numbers be referenced in a single row in another?

for example, if the health club "Kingsway" has 4 of those facilities, can I show in the row relating the "Kingsway" have under the facilities column ID numbers 1,2,5,6?

Cheers

  • 2
    Of course. Use something like SELECT ClubID, GROUP_CONCAT(FacilityID) ... GROUP BY ClubID. – Akina Jan 29 at 6:14
  • One of the goals of sound relational database design is to provide keyed access to all data. To understand why this is a goal you have to understand why keyed access is better that table sweeps. This goal is formalized in First Normal Form. Read up on this as part of your learning. – Walter Mitty Jan 30 at 12:04
1

While it is possible to have a single row for Kingsway and a column for each facility it would be a poor design.

Better to have one table to list clubs. A second table holds the superset of all possible facilities across all clubs. A third table shows which facilities each club actually has. It will have a row for each (club, facility) pair e.g. (Kingsway, pool), (Kingsway, bicycles), (Kingsway, sauna) etc. If another club also had the same facilities there would be a further three rows in this table: (Club2, pool), (Club2, bicycles), (Club2, sauna).

How these tables become populated depends on the source of the data and the particular database design chosen. If the data comes from off-line sources, say brochures, there's no alternative but to type it in to each of the three tables. If the source is digital it could be loaded as-is into a disposable staging table. The appropriate parts are then copied to each of the three data tables. Finally the staging table can be dropped.

The final design could deploy either natural keys or surrogate keys. Using natural keys the third, linking table will appear exactly as shown above. The actual string "Kingsway" and the actual string "pool" will be in the linking table. Using surrogate keys the club and facility tables must be loaded first to creating the pairings between values in columns club_name and club_id, and between facility_name and facility_id. Then the linking table can be populated with the correct (club_id, facility_id) pairs. Likely this will need to join the staging table to the club and facility tables.

  • Thanks Michael, I guess that's kind of what I was looking at. A table that holds the Club data, i.e Name, Address, Etc. A table that holds what each club could potentially have, i.e sauna, cycle, pool A table that holds which club offers which facilities. What I guess I am having trouble doing is working out how to get the data into the last table. Would that be a manual data entry situation? And say if "Club1" and "Club2" both have cycle and Pool, are they stored in separate rows? – m00se Jan 29 at 9:55
  • @m00se I've edited in the further responses. – Michael Green Jan 30 at 2:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.