| Author |
Topic |
|
soft-c
Starting Member
3 Posts |
 Posted - 2013-03-13 : 16:18:55
|
| i have about 12 state that there are many property in states.it is possible to add a new state with some property .in example :state a: color,count,...state b,...but all property are difference.now i want to design tables and views.1- design a complete table with 80 fields(12 state*6 property) and then makes 12 views from table(12 state)2- design 12 tables and join then for a compelete view .which one is better .thnaks. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-03-13 : 16:48:07
|
| >>which one is betternone of the above. how about 3 tables 0 views:state (stateid PK, stateName) - one row for each stateproperty (propertyid PK, propertyName) - one row for each propertystateProperty (stateid, propertyid) PK is (stateid, propertyid) - association tableBe One with the OptimizerTG |
 |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-03-13 : 23:50:45
|
| Isn't this the dreaded EAV (anti)pattern?What do you need to do with each state? Or each property? Why are they dynamic and what do they represent? |
|
|
|
soft-c
Starting Member
3 Posts |
Posted - 2013-03-14 : 01:29:27
|
| thanks for replies.i want to edit my question.i do not need dynamic table.think i have a table with 80 fields.so which one is better?1- a table with 80 fields an then make a view from it.(in this part , maybe many fields be empty in every record ).2- a table with 80 fields and make 12 views from it .(it is like part 1)3- 12 tables and then a view from that tables(in this part need big join).4- 12 table and then make 12 view and then make a view from 12 views.(it is like part 3)which have high performance and easy to work . thanks. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-03-14 : 06:27:05
|
| It's really not clear why you'd want this and what you're trying to achieve.So far you've implied that the states are all different and the properties are all different, hence your 80 combinations.If they're all different, they're not related so 80 rows is 'best' though I am sure that's not correct. I would imagine you'd only have one state table to cover 12 states but it appears they're not even the same (but somehow the differences are not properies either)What is in common and what is different betweena) Each stateb) Each propertyc) Each state/property combination. |
|
|
|
soft-c
Starting Member
3 Posts |
Posted - 2013-03-14 : 08:55:53
|
| there are 12 state:for example state a:it has 6 property like id,code,name,color,...state b:it has 6 other property like id,size,kind,...and other state.so all state are for a project .they have to join together to make a project.and i need all state with their property in a table .when user enter data it is maybe many property will be empty because that project does not have that state.can i explain good? |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-03-14 : 09:18:02
|
| If the basic structure of a state is the same then you need one state table with 12 rows.Depending on what you want to do you can - have 12 property tables (because the states represent different types of state)- use an XML type which gives you the ability to hold different types and index etc.- use SQL_VARIANT type.- TG's suggestion might also be good but it's dangerously overused a lot because it's obvious and many peoples' first choice.Not sure - it depends and I still don't really understand your requirement, inputs or outputs. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2013-03-15 : 19:03:52
|
| I'm a bit confused. You say that a "state" has six "properties" but that your table has 80 columns. Can you unconfuse me? Perhaps so examples would help.=================================================There are two kinds of light -- the glow that illuminates, and the glare that obscures. -James Thurber |
|
|
|
|
dynamic database