Databases
StackOverflow2013
Postsdbo
POstore static data in Databse or file system
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POstore static data in Databse or file system
    primarykey
    data
    text
    <p>Currently we are storing static data(e.g. gender, marital status, state etc..) into DB by creating different lookup tables (gender, state etc....) We have almost close to 10 lookup tables. All these tables are FK to transaction table - user(which stores all users demographic info). Some of these static data are also used by other transaction tables. Now while fetching a user info we normally put a join to these tables and get the related text. Certainly these many joins can create performance issues and also as per MSDN we shouldn't have more than 5 joins in a select query. Now to avoid this we can move all the static data to xml file and read it from them. Certainly we will cache the xml data so it will be only read once. I would like to know is that a good approach. I can see the below trade offs- 1) Cannot maintain referential intergrity. 2) while using load balancing all the servers need to have the xml file.</p> <p>I don't want the values to be in enum as these needs to be localized. the only advantage i see keeping in xml is that it reduces overhead to DB(creating 10 tables). I can very well cache the static data stored in DB. So would like to know that still storing data in xml is a good choice over DB? </p> <p>PS: Application is Web app and developed in .net 3.5.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPunit
    1. USPunit
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POstore static data in Databse or file system
      1. USPilgerstorfer Franz
      1. VTFavorite
    2. VO
      singulars
      1. POstore static data in Databse or file system
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POstore static data in Databse or file system
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI think the MSDN statement on the number of joins should be considered a guideline, and not a rule. Is there a specific problem that you're wanting to solve? Are these queries currently performing slowly? If so, there may be other approaches that you could take to improve performance, such as indexing, caching within your application, and finally denormalization as just a last-ditch solution.
      singulars
      1. POstore static data in Databse or file system
      1. USMichael Fredrickson
    2. COas of now no as the data is low but certainly it will come up when we will have 25k rows into the table. Moreover just to make it clear how the data will store in user table- relevant id of static data(e.g. user table will have column stateid and which will hold id of the state)will be stored for that user, static text will be stored in xml and will retrieve the appropriate text based on the id.
      singulars
      1. POstore static data in Databse or file system
      1. USPunit
    3. CODisclaimer: The following is MY own shortcuts that I have developed to speed up these types of issues based on years and years of db stuff going back to dBase. I key lookup tables that are usually static and have small row counts by the string(i.e. PhoeType: Mobile, Home, Work, etc). I do not key them by an integer ID. Then you do not need the join to display the text, but it will still enforce integrity for you and provide lookups as needed.
      singulars
      1. POstore static data in Databse or file system
      1. USGDF
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload