CODEWITHSUNDEEP
c++qt4sqlite
Gandalf
Gandalf

Reputation: 13693

enums alternatives

i want to store the colors of a team but unfortunately i am using sqlite which do not support enums.I want to store the team colors like this:

id   |   team name   |  team colors

1      vikings           red,orange,white

2      angles            gold,black

What's the way to go?.

Upvotes: 1

Views: 954

Answers (4)

luis.espinal
luis.espinal

Reputation: 10529

Easiest way (not necessarily the best under all conditions) is to create a table named colors (for example):

table color
id | color
1    green
2    red
3    blue
....

That's your enum table. Your c++ enums can be casted into ints which can then be used as primary key values. But if you are storing this data in a relational store, I don't think you want to tie these to a code-level artifact like an enum. A change in the contents of the table must force a change in the code.

Then, in typical normalized fashion, you create a relational attribute table, to represent the many-to-many relationship between your teams and the color enums. The color_id field maps to the color's table primary key, and the team_id maps to the teams table primary key.

table team-colors (color_id + team_id == primary key)
color_id | team_id
1          1
2          1
2          2
3          2
....

And then you have your team's table

table teams
id    | name 
1       vikings
2       angles
....

to know the colors per team, have a view (or run a SQL) with the following SQL pseudocode 

SELECT a.id as team_id, a.name, b.color, b.id as color_id
FROM teams a, colors b, team_colors c
WHERE a.id = c.team_id and b.id = team_colors.color_id

There are a lot of syntactic details that need to be worked on with this (and there are conditions where this might not be desirable, too many to discuss in one single post). However, this is the general idea, more or less.

Upvotes: 3

hookenz
hookenz

Reputation: 38917

Maybe a bit of overkill but how about a structure like this?

COLORS
id  color
1   red
2   orange
3   white
4   gold
5   black

TEAMS
id | team name | team colors
1    vikings     1
2    angles      2

TEAM COLORS
teamid | colorid
1        1
1        2
1        3
2        4
2        5

Some simple sql will then produce the list of colors you need.

On the other hand, you can just store the color names as you have. You'll have to parse them in your application.

Upvotes: 4

Elrohir
Elrohir

Reputation: 1486

Enums can be casted to integers. Say you have 

enum Color { red = 1, orange = 2, white = 4, gold = 8, black = 0x10 };
Color teamColor = red | orange | white;
Then you can store the teamColor as integer (int)teamColor.

Upvotes: 2

Buurman
Buurman

Reputation: 1984

An enum is essentially an int, why not store int-values then?

Upvotes: 0

Related Questions