db table scheme-what to do

Question

I have a table where specific service providers will be store there-car shops for example. The car shops offer various services.

My problem how to store in a column these services? I could create a second table where the services are stored but that would needed if I wanted to provide search functionality-which my intention is not to, at least not now.

So how I would store all the services in one row(that corresponds to a specific car shop for eaxmple). It would be of the VARCHAR type as I see it.

Furthermore is it OK that a primary key in a table references a primary key in another table?

WHat else do I have to do?

I need to stress also that there might be cases where there might be a unique service offered by a service provider-that only one service provider offers a specific service.

[Don't store multiple values in one field](http://en.wikipedia.org/wiki/1NF). See also [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables). — DCoder, Oct 30 '12 at 11:36
I read the wikipedia article, I understood it in theory but I did not quite understand the example with the telephone number. I do understand that if a column has multiple values(multiple telephone numbers) is a violation of first normal form. But how it is a violation if it contains only one telephone number. — Dimitris Papageorgiou, Oct 31 '12 at 07:37
I think I got it now, I looked more closely the examples. As a sidenote...This scheme where an id is used in a table to link two tables(in the wikipedia example-the customerID), is it a characteristic of innoDB tables or MyISAM? — Dimitris Papageorgiou, Oct 31 '12 at 07:55
It's a "characteristic" of any relational database. However, InnoDB is better than MyISAM at it - a relational database should support [foreign keys](http://en.wikipedia.org/wiki/Foreign_key) to enforce data integrity, but [MyISAM completely ignores them](http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-foreign-keys.html). — DCoder, Oct 31 '12 at 08:03

score 1 · Accepted Answer · answered Oct 30 '12 at 11:38

1

you can have many-to-many relationship,

This table stores list of service providers.

CREATE TABLE ServiceProvider
(
    ProviderID INT NOT NULL,
    ProviderName VARCHAR(50),
    .......,
    CONSTRAINT tb_pk PRIMARY KEY (ProviderID),
    CONSTRAINT tb_uq UNIQUE (ProviderName)
);

CREATE TABLE ServiceList
(
    ServiceID INT NOT NULL,
    ServiceName VARCHAR(50),
    .......,
    CONSTRAINT tba_pk PRIMARY KEY (ServiceID),
    CONSTRAINT tba_uq UNIQUE (ServiceName)
);

this table maps the list of service provider to their services offered.

CREATE TABLE Provider_Service
(
    RecID INT,
    ProviderID INT NOT NULL,
    ServiceID INT NOT NULL,
    CONSTRAINT tbb_pk PRIMARY KEY (RecID),
    CONSTRAINT tbb_fk1 FOREIGN KEY (ProviderID) 
          REFERENCES  ServiceProvider(ProviderID),
    CONSTRAINT tbb_fk2 FOREIGN KEY (ServiceID) 
          REFERENCES  ServiceList(ServiceID),
    CONSTRAINT tba_uq UNIQUE (ServiceName, ProviderID)
);

answered Oct 30 '12 at 11:38

John Woo

258,903
69
498
492

so you are suggesting that I create 3 tables? And another important thing I must say. There are many types of service providers, not only car shops, doctors also for example. Do you think I should create a separate table for each service provider? – Dimitris Papageorgiou Oct 31 '12 at 07:02
Furthermore...I have not yet understand why CONSTRAINED is used for,it is an error handling feature, but the details of it...still trying to figure out – Dimitris Papageorgiou Oct 31 '12 at 10:59
Are you using the CONSTRAINT keyword to declare that "this column will be the foreign key"? – Dimitris Papageorgiou Oct 31 '12 at 11:26
In the service provider table(for example) why do you index the the provider name. Is in it enough that you already have a primary key-which acts as an index either way? – Dimitris Papageorgiou Oct 31 '12 at 20:42
What is recID in the third table? – Dimitris Papageorgiou Nov 01 '12 at 13:29
oh, `recID` is just an autoincrement value for the row. but you can have composite primary key. – John Woo Nov 01 '12 at 13:32
I do not understand..I use PHPMyAdmin and whenever i wish a column to be autoicremented I just tick the corresponding option and that's it. I do not see anywhere any name name required for that, such as you using here RecID. – Dimitris Papageorgiou Nov 03 '12 at 11:32
regsrding this line here CONSTRAINT tba_uq UNIQUE (ServiceName, ProviderID)...have you made a mistake and where you mention ServiceName you accually meant ServiceID – Dimitris Papageorgiou Mar 04 '14 at 16:05

db table scheme-what to do

1 Answers1