I have a master table which would contain Price History for some entity.

An identity field as PK, a reference Id to that entity master record, and then the pricing, from and to date - which are easy to store.

I also need to keep a record of what "days of week" the price is active on, and also what "customer types" the price is for, and what "order types" it is valid for. Clearly I need to store a 1 to many relation for each record (for days of week, customer type, order type attributes).

What is the best design philosophy to implement this ?

Hi SQLDev,

You highlighted 1-M relations, what are your entities and what are their attributes?

One possibility is to store that type of data as XML columns in the price history table.

>> I have a master table which would contain Price History for some entity. <<

The term Master is not part of RDBMS; it belongs to files and network data bases. 

>> An IDENTITY field [sic] as PK, a reference Id to that entity master [sic] record [sic], and then the pricing, from and to date which are easy to store. << 

IDENTITY is not a field, or even relational; it is a table property and we never use it as a PRIMARY KEY. It is an exposed physical locators, while a key is a subset of attributes of the entity. There is no such thing as a generic id in RDBMS. It has to be <something in particular>_id by ISO_11179 and L8 Meta data rules. 

If you would follow the forum rules and post DDL instead of begin vague and rude, we could help more. 

>> I also need to keep a record [sic: history would be a better term ] of what "days of week" the price is active on, and also what "customer types" the price is for, and what "order types" it is valid for. <<

Rows are not records, fields are not columns and a day of the week will not work. Do all the Mondays always and forever have the same current price? Not likely.  

>> What is the best design philosophy to implement this? <<

Time is a continuum, so you will store (start_date, end_date) to model the interval when a price was in effect. Read this:

https://www.simple-talk.com/sql/t-sql-programming/contiguous-time-periods/ 

>> I have a master table which would contain Price History for some entity. <<

The term Master is not part of RDBMS; it belongs to files and network data bases. 

>> An IDENTITY field [sic] as PK, a reference Id to that entity master [sic] record [sic], and then the pricing, from and to date which are easy to store. << 

IDENTITY is not a field, or even relational; it is a table property and we never use it as a PRIMARY KEY. It is an exposed physical locators, while a key is a subset of attributes of the entity. There is no such thing as a generic id in RDBMS. It has to be <something in particular>_id by ISO_11179 and L8 Meta data rules. 

If you would follow the forum rules and post DDL instead of begin vague and rude, we could help more. 

>> I also need to keep a record [sic: history would be a better term ] of what "days of week" the price is active on, and also what "customer types" the price is for, and what "order types" it is valid for. <<

Rows are not records, fields are not columns and a day of the week will not work. Do all the Mondays always and forever have the same current price? Not likely.  

>> What is the best design philosophy to implement this? <<

Time is a continuum, so you will store (start_date, end_date) to model the interval when a price was in effect. Read this:

https://www.simple-talk.com/sql/t-sql-programming/contiguous-time-peri

Mr Celko, 

So basically, it is "NEVER" a good practice to use Identity property against a primary key column/field ? (what is the difference between a column and a field in SQL terms?)

Considering a very basic table - 

CREATE TABLE dbo.employee (empid INT PRIMARY KEY, empname varchar(64), empaddressLn1 varchar(256));

I want to be able to generate empId every time a new employee  row (record?) is added to the table.

(what is the difference between a row and a record as per you?)

Are you suggesting I do the following every time I have to generate a new empId value ? - 

SELECT MAX(Isnull(EmpId,0)) + 1  FROM dbo.employee;

Is using an Identity against a Primary key column an absolute NO GO ? 

I would like to post this question to most SQL GEEKS on this forum, apart from the obvious Mr. Celko.

Thanks.

• Edited by SQLDev198155 3 hours 13 minutes ago

Miss Naomi, 

Considering the entity here is "StoreMenuId" (meaning a key which references a menu item in a particular store)

I could end up having hundreds of thousands of rows (records) in the table.

And considering the "days of week" could repeat for different rows, isn't this going to be an issue of redundancy? 

Same with "Order Type" & "Customer Type".

I would also appreciate your thoughts on the pitfalls of using the Identity attribute against a Primary key field (as I posted in my other response on this thread).

Thank You

P

Mr Celko, 

So basically, it is "NEVER" a good practice to use Identity property against a primary key column/field ? (what is the difference between a column and a field in SQL terms?)

Considering a very basic table - 

CREATE TABLE dbo.employee (empid INT PRIMARY KEY, empname varchar(64), empaddressLn1 varchar(256));

I want to be able to generate empId every time a new employee  row (record?) is added to the table.

(what is the difference between a row and a record as per you?)

Are you suggesting I do the following every time I have to generate a new empId value ? - 

SELECT MAX(Isnull(EmpId,0)) + 1  FROM dbo.employee;

Is using an Identity against a Primary key column an absolute NO GO ? 

I would like to post this question to most SQL GEEKS on this forum, apart from the obvious Mr. Celko.

Thanks.

• Edited by SQLDev198155 Friday, August 21, 2015 3:54 AM

Can you post DDL (CREATE TABLE) statements?

Generally:

for 1 to many relationship you design FOREIGN KEY reference

for many to many relationship you design JUNCTION TABLE

Reference: http://stackoverflow.com/questions/14676342/ms-sql-creating-many-to-many-relation-with-a-junction-table

>> (what is the difference between a column and a field in SQL terms?) <<

Read the ANSI/ISO Standard SQL document, A field in SQL is part of a temporal value -{YEAR, MONTH, DAY, HOUR MINUTE, SECOND} , a very specific meaning in the language. But more than that, a field is physical storage while a column is logical. A column can be virtual or computed and have no physical existence at all. Or the column can be in records on a RAID drive and appear in many physical locations. Fields have no defaults , no constraints, no DRI and any data type is assigned in the application layer by the program reading it. 
>> Considering a very basic table -  <<

You also do not know ISO-11179 rules or basic data modeling. A table models a set, so its name is a plural or collective noun. Your mindset is still back in file systems. They work with one record at a time, which models a single employee, like you did. What math do you do with an employee identifier? None! So it should not be a numeric. The USPS standard for address lines is 35 Characters (this is based on a 3.5 label with 10-pitch type). Leaving out the usual CHECK() stuff, is this what you meant? 

CREATE TABLE Personnel -- see the level of abstraction? 
(emp_id CHAR(10) NOT NULL PRIMARY KEY, -- industry or government id
 emp_name VARCHAR(35) NOT NULL,
 emp_address_1 VARCHAR(35) NOT NULL,
 emp_address_2 VARCHAR(35) NOT NULL,
 city_name VARCHAR(2) NOT NULL 
 state_code CHAR(2) NOT NULL.
 zip_code CHAR(5) NOT NULL);

>> I want to be able to generate emp_id every time a new employee  row (record?) is added to the table.
(what is the difference between a row and a record as per you?) <<

Logical versus physical storage again. Google around for an old article of mine with a few thousand words on this. And you do not want to generate an identifier; you want to discover it. The entity already exists, so  unless you are making synthetic personnel and have to assign a serial numbers, it should be there already. In the case of personnel, you will need a tax number or other legal identifier. You do not want to tell the government that you sequentially number your employees, based on when they are hired at this location! 

>> Are you suggesting I do the following every time I have to generate a new emp_id value ? - 
SELECT MAX(COALESCE (emp_id, 0)) + 1  FROM Personnel; <<

A key is a subset of attributes of an entity which is unique. Uniqueness is just one property of a key. But this is just math on am incorrectly designed column. Ugh! Your mindset is still back the 1960's and pointer chains that serve a physical locators for the data on one disk on one machine. IDENTITY is the old Sybase/UNIX record numbering, not RDBMS. 

RDBMS is a whole different model of programming and data. 

>>CREATE TABLE Personnel -- see the level of abstraction? 
(>>>emp_id CHAR(10) NOT NULL PRIMARY KEY, -- industry or government id

Do you really think we would generalize  and define CHAR(10)  for emp_id ????