SQL design tips, comma-delimited list

First:

What kind of data should be kept in the main database before starting coding, with the best data organization and internal correlation.

Second:

Implement data management as efficiently as possible with the database features you know. such as the correct index, database type, efficient select!

--------------------------------------------------------------------------------------------------------------- ------------------

Some of the database problems caused by the comma-delimited list, the Coders table user record programmer , and the programming language that he uses are just some of the issues related to this topic.

CREATE TABLE Coders (--Programmer's Table

Name varchar (primary key)--name

Languge varchar (8)--programming language

);

In general, only one language can be used in the Department of Industry. For example, I use SQL. Other words

Insert into coders (Name,languge) VALUES (' Shang brother ', ' SQL '), so the line can be expressed. Okay, here's the problem. In general, it's going to be a "all rounder", which means I might as well write C #.

So the insert can not be written like this, to be the same as the following to express good.

Insert into coders (Name,languge) VALUES (' Shang brother ', ' SQL, C # ');

Problem:

1, scalability is not good, it is said that if the company is not just to ask you to SQL, C #, HTML, XAML, JS 、、、、、 and so on so this column is not so much to save.

2, for aggregation inconvenient, for example, statistics Shang elder brother will several languages? What you want to write.

3, database integrity can not be protected, insert into coders (Name,languge) VALUES (' Shang brother ', ' Hunan dialect '); see, "Hunan dialect" is not a programming language for the time being. When this is the insert is not

The error.

4, performance problems, such as the C # Programmer to find the select Name from coders where languge like '%c#%; you can see that this select is indexed and not available.

Solution:

CREATE TABLE languges (langugeid int primary key,langugename varchar (8));--Create a table of programming language tables to refer to this table.

CREATE TABLE Contact (

Langugeid int,

Name varchar (8),

Constraint Fk_languge foreign KEY (Langugeid) references Languges (Langugeid),

Constraint fk_name foreign key (name) references COrders (name));

　　

SQL design tips, comma-delimited list