This database will store a list of children. But the problem is, they will have their weight measured once a day. How can I store the changes so I can easily query their actual weight and the weight variation over one day, one week and one month? ...
I have a basically sql select question that people gave me different answers over the years. Say I have a couple of tables designed each with over 40 columns and potentially will hold ten and thousands of row, I'm using SqlServer2005. On joining these tables, in the where clause if I have things like select * from t1, t2 where t1.User...
I'm currently designing a database for a project. Now I'm debating with myself whether I have to create a look-up table for example 'civil status' data which can only contain fixed values like Single, Married, Separated, Widow/Widower. I'm pretty sure that no other values will be added in the future. Should I put these on a separate tabl...
I have this table in my SQL Express 2005 database: CREATE TABLE [dbo].[test_sort_order]( [Col1] [int] IDENTITY(1,1) NOT NULL, [Col2] [nchar](50) COLLATE French_CI_AS NULL, CONSTRAINT [PK_test_sort_order] PRIMARY KEY CLUSTERED ( [Col1] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [UQ_test_sor...
Do you know a good source to learn how to design SQL solutions? Beyond the basic language syntax, I'm looking for something to help me understand: What tables to build and how to link them How to design for different scales (small client APP to a huge distributed website) How to write effective / efficient / elegant SQL queries ...
I'm trying to design a data model that denotes one user being the friend of another user. This is what i've come up with so far, but it seems clunky, is there a better solution? User ===== Id Name etc... UserFriend =========== UserId FriendId IsMutual IsBlocked ...
I am creating a database using MySQL 5 for an eCommerce web site. I want the database to be as flexible as possible so that the owners of the web site can add custom attributes for various types of products. For instance, they can have a product which has 4 shirt sizes and 3 colors for each size available, or a product that has 6 shirt...
ORACLE does not permit NULL values in any of the columns that comprise a primary key. It appears that the same is true of most other "enterprise-level" systems. At the same time, most systems also allow unique contraints on nullable columns. Why is it that unique constraints can have NULLs but primary keys can not? Is there a fundament...
What are the tips/techniques when you need to persist classes with inheritance to relational database that doesn't support inheritance? Say I have this classic example: Person -> Employee -> Manager -> Team lead -> Developer -> Customer -> PrivilegedCustomer -> EnterpriseC...
I am pretty well versed with SQL Server, MySQL, Oracle etc but putting these Database products aside, is there a resource that will help me design relational databases well? Is there something like patterns or best practices for database design? I have seen a few times that database is often not scalable; people have personal preference...
If I have a customer table, it will store name, address, email address, phone number, and perhaps even some details about the customer like age, preferences, etc. Would I be doing a good thing if I partition this into smaller tables? E.g. customer_contact with the contact fields, and leave just the name, date of birth, etc in the origin...
Consider this example table (assuming SQL Server 2005): create table product_bill_of_materials ( parent_product_id int not null, child_product_id int not null, quantity int not null ) I'm considering a composite primary key containing the two product_id columns (I'll definitely want a unique constraint) as opposed to a sep...
I'm quite new to database design and have some questions about best practices and would really like to learn. I am designing a database schema, I have a good idea of the requirements and now its a matter of getting it into black and white. In this pseudo-database-layout, I have a table of customers, table of orders and table of products...
My biggest hurdle with getting started with an MVC framework has to do with with the 1 model to 1 DB table concept. To me it's overly simplistic and unrealistic for anything beyond a simple app. Yet, MVC is in use everywhere, including this awesome StackOverflow site. As is typical, all of the code examples and tutorials i come across ar...
What is a good approach for the following: On a weekly basis, approximately 250,000 records representing transactions will be appended to a large table in an SQL 2005 database. It is required to augment this data and append it to another table. (For example, based on the client ID on the transaction, various data will be computed based ...
I'm upsizing an existing MS Access backend to SQL Server 2008 and, because we want to use SQL Server Merge replication, I'll have to change all current primary keys (currently standard autoincrement integers) to GUID. So here are the questions: Any recommendation on doing the change of primary keys from integer to GUID? Any recommenda...
I am trying to write a MySQL query that retrieves one record from table "projects" that has a one-to-many relationship with table "tags". My application uses 4 tables to do this: Projects - the projects table Entities - entity table; references several application resources Tags - tags table Tag_entity - links tags to entities Is it p...
Say I have at database table containing information about a news article in each row. The table has an integer "sort" column to dictate the order in which the articles are to be presented on a web site. How do I best implement and maintain this sort order. The problem I want to avoid is having the the articles numbered 1,2,3,4,..,100 an...
I'm several layers/levels above the people involved in the project I'm about to describe. The general requirement is for a web based issue management system. The system is a small part of a much larger project. The lead pm has a tech pm who is supposed to handle this portion of the project. The lead pm asked me if it's normal for t...
I am about to embark on the development of a web application project. I'd like to get something up early and often for early adopters to play with and feedback. But I envisage the data model changing as the project progresses and my understanding of the system improves. How should I manage the dilemma of updating the data model appro...