How to store a list in a db column

by

In a normalized relational database, such a situation is unacceptable. You should have a junction table that stores one row for each distinct ID of the FOO object and the ID of the Fruit. Existence of such a row means the fruit is in that list for the FOO.

CREATE TABLE FOO ( 
  id int primary key not null,
  int1 int, 
  int2 int, 
  int3 int
)

CREATE TABLE Fruits (
  id int primary key not null,
  name varchar(30)
)

CREATE TABLE FOOFruits (
  FruitID int references Fruits (ID),
  FooID int references FOO(id),
  constraint pk_FooFruits primary key (FruitID, FooID)
)

To add Apple fruit to the list of a specific FOO object with ID=5, you would:

INSERT FOOFruits(FooID, FruitID)
SELECT 5, ID FROM Fruits WHERE name="Apple"

More Related Contents:

  1. Which one of these Queries are wrong and which is correct (SQL) [closed]
  2. SQL Selecting from three tables
  3. Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc
  4. How to implement a many-to-many relationship in PostgreSQL?
  5. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  6. How can I schedule a job to run a SQL query daily?
  7. Reset identity seed after deleting records in SQL Server
  8. What is the difference between JOIN and UNION?
  9. Generate a resultset of incrementing dates in TSQL
  10. Database Design for Revisions?
  11. Does SQLite3 not support foreign key constraints?
  12. Address standardization within a database
  13. Which SQL query is better, MATCH AGAINST or LIKE?
  14. update one table with data from another
  15. How to Implement Referential Integrity in Subtypes
  16. Generate script in SQL Server Management Studio
  17. NOT NULL constraint over a set of columns
  18. What are projection and selection?
  19. Name database design notation you prefer and why?
  20. Can parameterized statement stop all SQL injection?
  21. How to check which locks are held on a table
  22. Do clustered indexes have to be unique?
  23. Use email address as primary key?
  24. SQL query to find record with ID not in another table
  25. Find duplicate rows with PostgreSQL
  26. How to compare dates in datetime fields in Postgresql?
  27. Functions vs Stored Procedures
  28. How do NULL values affect performance in a database search?
  29. Single or multiple databases
  30. Copy table structure to new table in sqlite3

Leave a Comment