September 22, 2012 at 8:42 pm
[Quote]L' Eomot Inversé
[/Quote]
I dont know if this true, but I heard NoSql is Database for Programmer, :D.
L' Eomot Inversé (9/22/2012)
That depends on what a customer is; sometimes if a customer is the thing that has a billing address, then if you want to have orders from non-registered customers who don't pay cash on order you need to have non-registered customers in the database. Equally if you link an invoice to a customer you need to have non-registered customers in the database. Personally I don't like the non-registered customer concept because recording a delivery address or a billing address probably needs a customer, and recording those addresses is registering the customer. You may well have to provide invoice details for tax purposes, and what tax is payable will often depend on where the order is delivered or on where it was billed. For example if I order something from a UK seller for delivery here I will not pay UK VAT on it; if I order something from a UK seller for delivery in the UK I will pay UK VAT on it, and the UK tax authorities, if they decide to audit the seller's tax declarations, will expect to be provided with full details for the relevant years' transactions which will include billing addresses and delivery addresses.
This is what I think, it's seem waste of space since I need to store new one, when next time this Non Customer place Order.
CELKO (9/22/2012)
Pick up a good book on Relational Basics, Fabian Pascal wrote one but it is out of print now. I like MANGA GUIDE TO DATABASE; I am not kidding, it is good. Then read Steve Hoberman on data modeling; he has some good intro books.
This is a very different kind of programming. I think that 80-95% of the work is in the DDL (CREATE TABLE, etc), not in the DML (queries, etc). If you think we are weird, talk to an APL or LISP programmer:-D
Right now, I'm a freelence, so I need to do 'all thing' by myself, including Database Design.
I'm sure I will buy some Database Design and Sql Server book, I just found you have writing books on this topic, can you recommended yours ?
September 23, 2012 at 11:49 am
CELKO (9/22/2012)
SQL .. nor can I express subset constraints cleanly (again SQL can only do it with triggers, ie in DML not in DDL).
Yes, you can. In the full ANSI/ISO SQL standard we have a CREATE ASSERTION statement which lets you put a CHECK() on multiple tables. Try a simple example; each employee must have a health plan and belong to a sports team.
CREATE ASSERTION Health_and_Sports
AS CHECK
(NOT EXISTS
(SELECT emp_id FROM Personnel
EXCEPT
(SELECT emp_id FROM Personnel
INTERSECT
SELECT emp_id FROM Health_Plans
INTERSECT
SELECT emp_id FROM Sports_Teams));
In T-SQL we can use a VIEW using a little-know feature, the WITH CHECK OPTION clause. It will force you access Personnel only thru this view, but it works.
CREATE VIEW Health_and_Sports
AS
SELECT P.*
FROM Personnel AS P
WHERE NOT EXISTS
(SELECT H.emp_id
FROM Health_Plans AS H
WHERE H.emp_id = P.emp_id
INTERSECT
SELECT S.emp_id
FROM Sports_Teams AS S
WHERE S.emp_id = P.emp_id)
WITH CHECK OPTION;
Thanks Joe, that's very useful. I don't know how I missed the abilityto do this with CREATE ASSERTION, maybe it's something newer in the standard than the last time I had an interest in the SQL standardisation process. I must get back up to date (difficult, now I'm mostly retired - getting hold of up to date standards documentation is much more expensive that buying licenses for MS software - but I have no excuse for the decade and a half before that other than that I was wasting too much time doing management instead of real work).
I should have spotted the way of handling this with WITH CHECK OPTION back when I needed it, 10 or 11 years ago, but I didn't - big failure on my part. The fact that it needs all access through a view isn't an issue for me (and never has been - I was always bloody-mindedly insistent that people have access to the schema only through stored procedures, which in turn may be restricted to working through views when that is appropriate - because I want to enforce essential logic in the schema, not in DML).
Tom
September 23, 2012 at 1:06 pm
L' Eomot Inversé (9/23/2012)
Thanks Joe, that's very useful. I don't know how I missed the ability to do this with CREATE ASSERTION, maybe it's something newer in the standard than the last time I had an interest in the SQL standardisation process. I must get back up to date (difficult, now I'm mostly retired - getting hold of up to date standards documentation is much more expensive that buying licenses for MS software - but I have no excuse for the decade and a half before that other than that I was wasting too much time doing management instead of real work).
Just in case you are not aware of it, there is some very good information at http://www.wiscorp.com/SQLStandards.html and a reasonable version of SQL:92 at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (CREATE ASSERTION is there).
September 23, 2012 at 7:23 pm
SQL Kiwi (9/23/2012)
[Just in case you are not aware of it, there is some very good information at http://www.wiscorp.com/SQLStandards.html and a reasonable version of SQL:92 at http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt (CREATE ASSERTION is there).
Thanks Paul. That's very useful. I wasn't aware of either of those references, which I guess demonstrates how lazy I have been.
Tom
September 24, 2012 at 3:44 am
CELKO (9/22/2012)
In T-SQL we can use a VIEW using a little-know feature, the WITH CHECK OPTION clause. It will force you access Personnel only thru this view, but it works.
Interesting, thanks for the example!
I have a different trick for that, using indexed views. You can find it here[/url].
It's a dirty trick, but it works.
-- Gianluca Sartori
Viewing 5 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy