Just wondering if anybody is up for a general theoretical discussion of database design, as non-technical as possible. I've created a few MySQL databases that are now in use, probably 8 or 10 by now, and they work perfectly well but I feel that a few things I've done are non-optimal work-arounds, a bit awkward. The design could be better. (I'll accept recommendations for excellent, readable, comprehensible books, too.)
Let me describe a very simple "straw man" database we can use to pick apart. Let's say it has only two tables. One is a list of universities, and one is a list of projects, in which universities take part.
The list of universities is always growing, as more and more projects appear that work with more and more universities. A project can involve any number of universities, zero or more.
Let's say the university table has two columns: univ_id (a unique identifier) and univ_name (the name of the university).
What is the best way to set up the project table so that any given project record can include multiple university IDs?
One crude way would be to add a new column in the project table for each university in the university table, and make it an ENUM('Y','N') field or something. But that's ridiculous. Nobody wants to alter a table structure each time a new university is added to the other table.
Another way would be to add a univ_id field to the project table, i.e., a "foreign key" or shared column, but one that could accept multiple university IDs somehow. So where the univ_id field in the university table might be an auto-incremented integer field, in the project table it might be a VARCHAR field that could take a value like '1+23+47+103' without adding them up, or '1,23,47,103' or whatever separator you wanted to use. Then you could parse those out and find out which universities were in the project.
I've done something like this latter way in a few projects. It just seems like there's something clunky about it. How else might this problem be solved? This seems like a basic situation for a database to have to handle, so I'm sure more experienced programmers have worked out exactly how to do it.
A related design issue has to do with the best (most user-friendly) way to get user input. If we've got 500 universities in our university table, we probaby don't want to generate a form with 500 checkboxes next to 500 university names. I guess you can make a drop-down list where multiple selections are permitted, but that's almost as clunky. There's the simple text input, but then if people spell something wrong it gets messed up, and there's the possibility of multiple slightly different names being entered for the same university.
Well, that's enough for now. If anybody is interested in this topic, whether it's to ask additional similar questions or to provide some insights with the benefit of experience, I'm all ears.
General discussion of database design
Moderator: General Moderators
About the tables and their realtion.[/i)
I assume this is an problem which happends quite offen. I should solve it by adding a new table, a relation table. It should contain two integer fields, projectid and universityid. So if a university is involved in two projekts, it should have two relation (two records).
I assume this is an problem which happends quite offen. I should solve it by adding a new table, a relation table. It should contain two integer fields, projectid and universityid. So if a university is involved in two projekts, it should have two relation (two records).
i have come upon this problem myself in a project i was doing, i had a series of locations, and ships which had to reside at a location (its a game)
so i created a table of fleets, and related all 3 tables:
location-fleet-ship
the ship reference had a fleet reference, the fleet reference a location ref.
im assuming also that you would be wanting to have more information about a universities involvement in a project (date entered, funding, etc) which this system enables...
basically you have a table of university details, a table of project details, and a table which details how each uni interacts with the project
i dont know how efficient it is cos im a newb, but it works
so i created a table of fleets, and related all 3 tables:
location-fleet-ship
the ship reference had a fleet reference, the fleet reference a location ref.
im assuming also that you would be wanting to have more information about a universities involvement in a project (date entered, funding, etc) which this system enables...
basically you have a table of university details, a table of project details, and a table which details how each uni interacts with the project
i dont know how efficient it is cos im a newb, but it works