SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 dynamic database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

soft-c
Starting Member

3 Posts

Posted - 03/13/2013 :  16:18:55  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 03/13/2013 :  16:48:07  Show Profile  Reply with Quote
>>which one is better
none of the above.

how about 3 tables 0 views:

state (stateid PK, stateName) - one row for each state
property (propertyid PK, propertyName) - one row for each property
stateProperty (stateid, propertyid) PK is (stateid, propertyid) - association table

Be One with the Optimizer
TG
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/13/2013 :  23:50:45  Show Profile  Reply with Quote
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?
Go to Top of Page

soft-c
Starting Member

3 Posts

Posted - 03/14/2013 :  01:29:27  Show Profile  Reply with Quote
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.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/14/2013 :  06:27:05  Show Profile  Reply with Quote
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 between
a) Each state
b) Each property
c) Each state/property combination.
Go to Top of Page

soft-c
Starting Member

3 Posts

Posted - 03/14/2013 :  08:55:53  Show Profile  Reply with Quote
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?
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 03/14/2013 :  09:18:02  Show Profile  Reply with Quote
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.
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1677 Posts

Posted - 03/15/2013 :  19:03:52  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000