services column

[jimfog]

suppose I want to a store in a table a column that will store services provided by a service provider.But I do not want to a separate row for each service, (because each row will be assigned to a separate service provider). I want the services listed in one row, for the reason I mention above. What kind of column typethat could be?

[thescientist]

VARCHAR(XXX) or some similar string based type, and then just store it as CSV. Or make two tables, one for service provider, and another table for services. each service will have a foreign key to the service provider table.

Edited October 24, 2012 by thescientist

[jimfog]

since I have not done such a column type before I need to make the following question. Will the size of the varchar depend on the size of the characters of the individual service or the total characters that might these occupy?

[birbal]

when you set varchar type to a column it counts the character of each row for that column. deviding the service and service provider to different table is the best way. if you implode all the service as text in service provider tablle it will not be efficient when query will search through the data.

[jimfog]

deviding the service and service provider to different table is the best way. if you implode all the service as text in service provider tablle it will not be efficient when query will search through the data.

Yes, but I am afraid that this will complicate the table structure. I do not saying that you are not right about the query. My motivation is that it is better to have all the data related to a service provider in one table(services, name, address etc...)...but I maybe opt for your solution after all.

[justsomeguy]

If you want to search for all providers who provide a particular service then your design won't work for that. That will only let you list all services provided by a particular provider, but you can't search for any provider who provides a particular service. You need a lookup table for that, where you store the providers in one table, all possible services in another table, and a third table to link them and say which providers provide which services.

Yes, but I am afraid that this will complicate the table structure.

Since you haven't heard of varchar before, which is the main way people store small strings of text, it's probably fair to say that what you think is complex is actually relatively simple as far as the database world goes.

[jimfog]

let us forget the searching feature for now.I know what a varchar is. I suppose you are mentioning varchar for the case I use one table after all and varchar will be the column type for the services. I say the db will become complicated when I opt for the scheme you are describing-multiple tables.

[justsomeguy]

I'm saying that your definition of "complicated" is relative. To me, 2 tables with a lookup table is not "complicated", it is "necessary" for a many-to-many relationship that you want to store in a meaningful way in the database. If you have a one-to-one relationship then you can use a single field to store the relationship, if it is a one-to-many relationship between services and providers then you can still use a single column in the providers table. If you have a many-to-many relationship that you actually want to store in a meaningful way then you use lookup tables to avoid having to write multiple WHERE predicates for each search. To me that is not complicated, that is the easiest and most straightforward way to represent a many-to-many relationship in a relational database. Any other solution will involve you duplicating data and storing data in a way that you can't use with join queries, which you can't optimize with an index, and which you can't write a search query without duplicating the WHERE predicates. You don't have to take my word for it though, people learn by designing something and then figuring out where it fails. Go ahead with your plan and you'll find out where it fails.

[jimfog]

I'm saying that your definition of "complicated" is relative. To me, 2 tables with a lookup table is not "complicated", it is "necessary" for a many-to-many relationship that you want to store in a meaningful way in the database. If you have a one-to-one relationship then you can use a single field to store the relationship, if it is a one-to-many relationship between services and providers...

First of all,because you are using terminology not so much familiar to me(I know some stuff though), some clarifications are needed. You are mentioning the term one-to-many relationship. Am I right to assume that when we have 1 provider and many services, that this is an example of one-to-many.Am I correct? So you are saying that with one-to-many I can still go with my original plan-one table only.Although I do not know how am I going to store all the services in one column. It would be certainly varchar,but I have no idea about the csv part mentioned above.Any hint would be helpful here.

You don't have to take my word for it though, people learn by designing something and then figuring out where it fails. Go ahead with your plan and you'll find out where it fails.

You are absolutely correct here.

[jimfog]

VARCHAR(XXX) or some similar string based type, and then just store it as CSV. Or make two tables, one for service provider, and another table for services. each service will have a foreign key to the service provider table.

As I see there is no way of having individual columns in CSV. The whole table must be in CSV.

[justsomeguy]

Am I right to assume that when we have 1 provider and many services, that this is an example of one-to-many.Am I correct?

One-to-many means that one provider can have many services, and each service is only provided by 1 provider. Or that each service can be provided by many providers, but each provider has 1 service. Many-to-many means one provider can have many services, and one service can have many providers. CSV means comma-separated values, it's a string of text that contains several values separated by commas. That's all it is, it's not an actual data type or format that you set up in the database, it's just text in a particular format that you know how to deal with. It doesn't provide any advantages with SQL. If you're searching for a single value in a comma-separated field it requires at least 3 WHERE predicates to cover the possibilities.

[jimfog]

Suppose I go for 2 tables plus the lookup(service providers, services, lookup for these 2). I assume I must foreign keys, which means using innoDB, correct?

[justsomeguy]

If you want the database to enforce foreign key constraints then you need to use InnoDB. I don't have the database do that, I typically enforce that in my application.

[jimfog]

And how you can enforce to do that, without leaving that to the database...I am curious to hear how do you do it. And more important, are there any advantage if you do it yourself instead the db?

Edited November 1, 2012 by jimfog

[justsomeguy]

It's just checking to make sure the data is correct when you're inserting, updating, and deleting. The advantage is I can show my own error message instead of the mysqli extension coming back with a fatal error.