Datatype equivalent to SET in a database?

Question

I want to use a SET datatype for my databse. So that a field of that type can contain one or more values from that data type.

But I have following two questions:

Q1. Is SET is correct to use as a datatype in a database? I think that its not supported by all the databases.

Q2. If SET is not a good option, then what can I use in place of SET?

Answer 1

You're looking at enumerated types, but not all databases support these. What is your flavour of database?

Answer 2

You should use a table for this with a foreign key:

YourTable
col1 ...
col2 ...
YourTypeCol  char(1) FK to YourTypeTable
col4 ...

YourTypeTable
YourTypeCol             char(1) PK  <<make the data type fix your "set"
YourTypeColDescription  string

So for example, you'd have data like this:

CarTable
CarID      int PK auto number
CarMaker   int FK
CarBuilt   date
....

CarID  CarMaker  CarBuilt
1      1         1/10/2005
2      4         3/18/2004
3      3         10/31/2009
...

CarMakerTable
CarMake      int PK
CarMakeName  string

CarMake    CarMakeName
1          Ford
2          GM
3          Honda
4          Mazda
...  

as for the So that a field of that type can contain one or more values from that data type I would not recommend this. It is best practice to store only one value per "field". Storing multiple values in a single field is against the principle of Database normalization and will result in issues when you try to pull particular items out of the "set". It is best to split each value into its own row, which means changing your table design. Supply more information about the data you are trying to store and I can recommend a table structure for you.