Hello, Trying to better my grasp on this area of application design/architecture/development and looking for any suggestions in this area. I have read some other posts and have a list of development theory books, but would like to form a list for more architecture based books that relate to web based applications. Thank you! ...
i have a database that has a list of employees of an organziation. There is one main employee table and number of joined tables. i want to start to track week over week and month over month metrics on this data so i can do things like: March 1: headcount 100 (+1, -2 in Feb) April 1: headcount 101(+3, -2 in Mar) May 1: headcount 105(+1...
If i were to organise data on tourism and hotel for the continent of Australia and south America can you let me know a best way to do it. Like if i organise the tables thus: continents countries states zipcodes cities destinations hotels lnkhotelszipcodes (in case a hotel exists in multiple places) lnkhotelscities It will be search ...
My database has two tables, one contains a list of users, the other a list of roles. Each user will belong to one or more roles, and of course each role will have multiple users in it. I've come across two ways to link the information. The first is to add a third table which contains the ID's from both tables. A simple join will the...
Here's the background: Sales Agents sell insurance. They work alone or in an agency of 2 or more. When multiple agents work together, they need to split their commissions. The commission splits are predefined by percentages. Example Joe, John, and Saul belong to an agency. For every policy sold Joe gets 40% of the commision, John ge...
I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+...
I have always wanted to make a small easy to use CMS system just for personal use and training. First thing im going to do is create the data model. So what do I need to get started? I would like to dynamically create pages, then I need a Page table. I know many CMS systems reffer to them as nodes, so I'll call it Nodes. I would also ...
What I am trying to do is to add data for a product into a database with MySQL 5.1 and PHP 5.2.12. I have a table for general fields: name, description, price, number of times purchased, and also I have a table for the images that are associated with that product. The goal is to have the same id for every image that corresponds to its...
I have ran into this problem a few times and I am wondering what other people are doing. When I am creating a database, sometimes I have to import data into a table on a regular basis, let's say daily. What I normally do is delete all of the records and reimport every record from the external data source. Many times I will have to stor...
I have three basic types of entities: People, Businesses, and Assets. Each Asset can be owned by one and only one Person or Business. Each Person and Business can own from 0 to many Assets. What would be the best practice for storing this type of conditional relationship in Microsoft SQL Server? My initial plan is to have two nullabl...
When it comes to column order in DB tables, are there any standards or at least best practices? Here are a few handmade best practices that I've adopted: primary key comes first; foreign keys come after the primary key; columns holding user generated data come after the foreign keys; timestamp columns come in the end of the table (no ...
Im writting a RH system witch consist in two "services": Web App - For RH recruiting management companies Web site - For people and companies to seek/register CV The two things use the same databases for all data... my question is about the users... im the WebApp, the user HAVE to be attached in a Company (my clients).. .but in the w...
Hi there, not sure if the subject entirely conveys what I'm trying to achieve, but let me explain: We are building an application that uses Oracle as storage backend. Each year, last years dataset will be "Archived", and a new instance created and populated from scratch. What are the options to do this within the same schema? Keep ve...
I have this script(dtsx file) on a sql 2005 server (made by some other technician), some stored procedures update tables with data from views. I can see that these views are based on other views, sometimes 4 to 5 layers thick before hitting the database tables. Would you recommend to improve this construction? (when confronted with this...
As an example, I have a 3 tables: School: ID int, Name varchar Student: ID int, Name varchar StudentInSchool: StudentID int, SchoolID int Now the question is whether I should put a column ID int with a primary key on it in StudentInSchool table? If yes, why? Will it be helpful in indexing? Any help appreciated. ...
What are the pros and cons of using NULL values in SQL as opposed to default values? PS. Many similar questions has been asked on here but none answer my question. ...
What is the name for the technique of using a set of foreign keys in a table where all but one are NULL for a given row? In other words, each row needs a foreign key to one (and only one) of n different possible tables so you actually have all the necessary foreign keys but all but one are NULL. (users of Django may recognize this as a...
I need to work with several data samples, to say, N. The samples represent similar data but from different origins. For example, history of order in different shops. So the structure of all the samples is the same. To operate with the data I have several possibilities: Use N databases with identical schema, one for each sample Use one ...
I have a simple messaging system that up till now uses file based storage. Because the number of users of the system is increasing gradually I would like to switch over to database based storage. In code the message server maintains a list of users (with their credentials) and a list of messages. A Message object has as its main fields a...
I am developing an application which will be integrated with thousands of sensors sending information at every 15 minute interval. Let's assume that the format of the data for all sensors is same. What is the best strategy of storing this data so that every thing is archived (is accessible) and does not have a negative impact due to larg...