[PHP-DB] Having more than one id in the same field
Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Having more than one id in the same field
Add a third table: event table organization table responsibility table id eventID organizationID Now you can have more than one organization responsible for each event, and each organization can have one or more events. Just drop the organizationID from the event table, or leave it as the Primary Organization and everyone else is a sub organization... though that should probably be kept in the responsibility table, not the event table. Peter On Sun, 10 Nov 2002, Achilles Maroulis wrote: Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php --- Peter BeckmanSystems Engineer, Fairfax Cable Access Corporation [EMAIL PROTECTED] http://www.purplecow.com/ --- -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Having more than one id in the same field
I can't remember CD's 12 rules regarding relational databases, but a rough paraphrase of one of them is that you do not need to know anything about how data is stored to manipulate it, the database schema should provide all that information. Another one is that you never repeat information that can be broken out and stored in another table. You have to normalize your data, so in this case you would add a field, call it EventHost with fields PriKey EventId RspOrg EventId is a foreign key, referencing the field in the Event table which identifies the event, and RspOrg is a foreign key which references the key values in the, lets call it Organization (or Host) table. (PriKey is the primary key for the table, a field which hard experience has convinced me to have in all database tables, though in this case it may not be needed.) Indexes on these fields will assure that your queries will be optimized and you won't be condemned to sequential reads of the database while fields are evaluated by expressions like ...where '$id' in (events.ids)... . The Event table no longer needs a RspOrg field, and you can determine additional information about the host organization or the event through this table. You may also add fields such as HostLevel which indicates whether the RspOrg is a primary or secondary host, and so forth. It's time to Google for a primer on SQL databases and normalization, work through a couple, and to ask yourself what kind of questions you will be asking of your database. Regards - Miles Thompson At 12:41 PM 11/10/2002 +0200, you wrote: Hi. I have a database designing question to ask. I want to build a table of events. Among the other fields there must be a field that holds the 'responsible organization' of the event. This organization of course will be responsible for other events as well so I have to create another table that holds the organizations (id, name, phones, director etc) and then just pull the organization id to the events table. The problem is that it happens too often to have 2 organizations responsible for the same event so I'll have to add them both to the events table in the same record. How do you advice me to do that? I thought that I could use a text field to hold the ids and then when searching the database just change the MySQL command from ...where events.id='$id'... (As it would be if only one id was going to be used) to ...where '$id' in (events.ids)... or maybe something using LIKE. Do you think it can be done this way? Apart from the responsible organization I may have other fields in the same table having the same problem (for example: the event visitors are staying in one hotel and I want to hold control of the hotels as well. Maybe 2 hotels are used instead of one). If I solve my problem this way, do you think that it will be too difficult or 'heavy' to have more than one condition like this in my queries? Do you think of any other way? Thanx in advance Achilles -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
