How to solve the old bool attributes vs pivoting issue?
От | Andreas |
---|---|
Тема | How to solve the old bool attributes vs pivoting issue? |
Дата | |
Msg-id | 4FEBAE3D.4030202@gmx.net обсуждение исходный текст |
Ответы |
Re: How to solve the old bool attributes vs pivoting issue?
|
Список | pgsql-sql |
Hi I do keep a table of objects ... let's say companies. I need to collect flags that express yes / no / don't know. TRUE / FALSE / NULL would do. Solution 1: I have a boolean column for every flag within the companies-table. Whenever I need an additional flag I'll add another column. This is simple to implement. On the other hand I'll have lots of attributes that are NULL. Solution 2: I create a table that holds the flag's names and another one that has 2 foreign keys ... let's call it "company_flags". company_flags references a company and an id in the flags table. This is a wee bit more effort to implement but I gain the flexibility to add any number of flags without having to change the table layout. There are drawbacks 1) 2 integers as keys would probaply need more space as a boolean column. On the other hand lots of boolean-NULL-columns would waste space, too. 2) Probaply I'll need a report of companies with all their flags. How would I build a view for this that showsall flags for any company? When I create this view I'would not know how many flags exist at execution time. This must be a common issue. Is there a common solution, too?
В списке pgsql-sql по дате отправления: