database-design

Product table, many kinds of product, each product has many parameters

Hi, i'm have not much experience in table design. My goal is a product table(s), it must design to fix some requirement below: Support many kind of products (TV, Phone, PC, ...). Each kind of product has different set of parameters like: Phone will have Color, Size, Weight, OS... PC will have CPU, HDD, RAM... Set of parameters must ...

Representing Key/Value Pairs Where the Value Can Be of Any Type (in a Relational Database)

A small but important corner of a database that I'm designing will be used to store the result of arbitrary calculations. These results can be of any type. How can I represent a Value field that can be of any type in a relational database? The only thing I can think of is to have separate tables based on data type that all have foreign ...

How to represent implicit relationships?

Hi, I am developing an application where I have to deal with an Entity named 'Skill'. Now the thing is that a 'Skill A' can have a certain relevancy with a 'Skill B' (the relevancy is used for search purposes). Similarly 'Skill B' can also be relevant to 'Skill C'. We currently have the following data model to represent this scenario S...

Design of a database, based on a data structure

I have a data structure something like this: typedef struct tagSUB_DATA { double measuredValue; double standardDeviation; double calculatedValue; double weightedError; } SUB_DATA; typedef struct tagALL_THE_DATA { int aNumber; double aDouble; SUB_DATA measurements1; SUB_DATA measurements2; } ALL_THE_DAT...

Integrating two datasets... Help!

I'm doing some work to integrate two databases in two different places. One is an Access db and the other SQLServer. The size of the set is really quite small, say 350 records. This could be really straightforward however the two people that 'own' the datasets insist on being able to add new fields to the databases. Any ideas as to how...

Picking the best primary key + numbering system.

Hi All, We are trying to come up with a numbering system for the asset system that we are creating, there has been a few heated discussions on this topic in the office so I decided to ask the experts of SO. Considering the database design below what would be the better option. Example 1: Using auto surrogate keys. ================...

Company-Employee database design

I am trying to build a SQL 2005 database that has a Company data table and an Employee data table. Each company should have an Employee 1, Employee 2, and so on. I realize that each Employee record would have a PK of both their EmployeeID and their CompanyID, but I haven't for the life of me determined the best way of doing this? An...

SQL Server (2005+) query to return the base table and base column (field) for each column (field) in a view

I want a query that will return a row for each column in a view, and a row for the view itself. There should be a column basetable in the result that gives the base table for the column in the current row, and a column basefield in the result that gives the name of the column in the underlying query (for renamed columns). It would be a ...

Is this a good design for an audit table with tons of records?

I have a table that tracks inventory data by each individual piece. This is a simplified version of the table (some non-key fields are excluded): UniqueID, ProductSKU, SerialNumber, OnHandStatus, Cost, DateTimeStamp Every time something happens to a given piece, a new audit record is created. For example, the first time my product ...

Using custom codification scheme instead of GUID as Primary Key

I'm upsizing a backend MS Access database to SQL Server. The front-end client will remain an Access application for the time being (has about 30k lines of code). The aim is to eventually allow synchronisation of the database accross multiple servers (not using replication but probably the sync framework). Currently, all Primary Keys i...

LDAP Modeling Best Practices

I am very in tune with relational modeling but new to LDAP modeling and am looking for best practices for designing LDAP schemas. Would love to know what are the equivalents of third normal form and other practices in the world of LDAP? Links to white papers that expand on this subject are greatly appreciated. ...

Single-table-inheritance or two tables?

Hi, Suppose I have a table with the following columns (a list of words): word: varchar contributor: integer (FK) Now, suppose I wanted to have translations for each "word". What would be best? Having a second table? word: integer (FK) translation: varchar contributor: integer (FK) lang: integer (FK) Or all in the same table? word...

history rows management in database

hey guys, As in many databases, i am designing a database that should keep record of previous versions of the rows changed in each table. The standard solution to this problem is to keep a history table for each data table, and whenever a row needs to be updated in the data table, a copy of the current row gets inserted to the history ...

What are the principles behind, and benefits of, the "party model"?

The "party model" is a "pattern" for relational database design. At least part of it involves finding commonality between many entities, such as Customer, Employee, Partner, etc., and factoring that into some more "abstract" database tables. I'd like to find out your thoughts on the following: What are the core principles and motiva...

How can I automatically determining table(s) schema from a set of queries?

Is there any tool, which will take a set of CRUD queries, and generate a 'good enough' table schema for that set: e.g. I can provide input like this: insert username, password insert username, realname select password where username=? update password where username=? update realname where username=? With this input,...

'Belongs to' db relationship best practices?

I'm working on a photography site. Photos on the site will always belong to exactly one event. My initial design was: Table: Events ID, Title, etc Table: Photos ID, ThumbnailURL, etc Table: EventPhotos EventID, PhotoID, SortOrder This seems natural to me, but I realize the relationship it describes actually allows a Photo to belong ...

Database Design Question

I'd appreciate some opinions on a concern I have. I have a [User] table in my database, with the basic stuff you'd expect, like username, password, etc... This application requires that I track a vast number of attributes for each user. So much so, that I will likely run out of columns (row storage space). I'm tempted to add a UserPr...

Database structure question

I am currently working on an application which references the Microsoft Business Contact Manager SQL Server database add-on for Outlook. The main Contact table in the db has several sub tables which have identical keys to the main table and a small number of columns; for example, an email address table which has 3 columns: contactID, Em...

Where do new companies in IT Business developing softwares, websites and databases find work from?

Hi There, i am a qualified software engineer who has experience working on designing softwares, databases and websites but I don't understand how do companies find work for software database adn website development? Any advice will be much appreciated.. I do realise it may make me look dumb and may attract negative feedback but all throu...

Database design for point in time "snapshot" of data?

How to design a database that supports a feature that would allow the application user to create a snapshot of their data at a point in time, a bit like version control. It would give the user the ability to go back and see what their data looked like in the past. Assume that the data being "snapshotted" is complex and includes joins o...