On 04/08/2014 03:41 PM, Rob Sargent wrote:
> On 04/08/2014 03:36 PM, CS_DBA wrote:
>>
>> On 04/08/2014 03:31 PM, Rob Sargent wrote:
>>> On 04/08/2014 03:26 PM, CS_DBA wrote:
>>>>
>>>> On 04/08/2014 03:17 PM, Rob Sargent wrote:
>>>>> On 04/08/2014 03:09 PM, CS_DBA wrote:
>>>>>>
>>>>>> On 04/08/2014 02:58 PM, Rob Sargent wrote:
>>>>>>> On 04/08/2014 02:51 PM, CS_DBA wrote:
>>>>>>>> Hi All
>>>>>>>>
>>>>>>>> we have a table like so:
>>>>>>>>
>>>>>>>>
>>>>>>>> customer (
>>>>>>>> cust_id integer not null primary key,
>>>>>>>> cust_group_id integer not null,
>>>>>>>> group_account_id integer not null,
>>>>>>>> cust_name varchar not null,
>>>>>>>>
>>>>>>>> ...
>>>>>>>> )
>>>>>>>>
>>>>>>>> we want to force the cust_group_id to be unique across all
>>>>>>>> group_account_id's but not necessarily across the entire table
>>>>>>>>
>>>>>>>> I assume the best approach would be a check constraint yes?
>>>>>>>> Will this be excessively poor per performance if the table gets
>>>>>>>> big?
>>>>>>>>
>>>>>>>> Thoughts?
>>>>>>>>
>>>>>>>> Thanks in advance
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>> A unique index on cust_group_id and group_account_id doesn't do
>>>>>>> it for you?
>>>>>>>
>>>>>> oh right! duh! It's been one of those days....
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>> Which column goes first depends on your lookup expectations.
>>>>
>>>> Thanks!
>>>>
>>>>
>>>> Here's another one:
>>>>
>>>>
>>>> customer (
>>>> cust_id integer not null primary key,
>>>> cust_group_id integer not null,
>>>> group_account_id integer not null,
>>>> cust_name varchar not null,
>>>> cust_template_id integer,
>>>> ...
>>>> )
>>>>
>>>> If cust_template_id IS NOT NULL then it must reference a valid cust_id
>>>>
>>>> Check constraint?
>>>>
>>>>
>>> Nope. Useless column :). You already have cust_id so
>>> cust_template_id is either null or already known.
>>
>> Actually its a goofy design in the web app... users can enter the
>> template_id on the fly and if they do we want to enforce the fact
>> that it's a valid cust_id (meaning any existing cust_id can be used
>> as a template but made up template ID's - meaning an id that does not
>> match an existing cust_id should be disallowed)
>>
>>
>> Thoughts?
>>
>>
> Really goofy. They could type in any valid cust_id, theirs or not theirs.
> What are you after with template_id. How would your app use it. Why
> would user fill it in?
>
Not sure yet (new client)... for now they simply want to force the
template column to be a valid cust_id, if it is not null... later I'll
be digging into their design and pushing them to make some db
architecture changes...