Search results

I am extremely familiar with reading, and designing, database schemas. Exactly, and that's why it makes sense to have a uniqueness constraint on the composite of Supplier and Invoice Number, i.e. a composite key. Failing to do that would mean the database design would not be an accurate...

Without a uniqueness constraint on (Supplier, InvoiceNumber) your suggested schema would allow duplicate data and it would no longer be possible to identify invoices uniquely for each supplier. Maybe an invoice would get paid twice as a result (it happens more often than you think). That's why...

I would expect the combination of the supplier's identifier and the supplier's invoice number to be the composite key used by your users to identify invoices in invoice table. It ought to have a unique index on it. Is that what you mean by "ID field entry"? I wouldn't expect it to be just one...

What you can do is create an empty table with the autonumber column, then insert the existing primary key column into it when you insert all the other data. I'm assuming the existing PK is a number. If it isn't then maybe you could just add a sequential number before you do the insert.

Exactly. Software should reflect changing business needs. Very often change is the only constant. Your description of the software lifecycle looks like a Waterfall methodology. Many organisations have realized that Waterfall isn't such a good fit for the modern business environment...

Cost vs security risk is not such a big deal because commercial grade security is extremely cheap and easy to implement in most database architectures. The real dilemma here is not one of cost but of using the right technology: you can have a level of security that satisfies industry and...

Putting the dietary requirement attribute in the guest table doesn't (as far as I can see) break normalisation rules. If guest determines dietary requirement then under any Normal Form the dietary requirement attribute belongs in the guest table.

Agreed. But your original question was why have a key for an order detail table. An order detail table should have a key for the same reason as any other table: data integrity. For sure a "random", meaningless or poorly chosen key probably won't achieve much and is probably no better than having...

Any of the modern reporting tools have much more sophisticated features than the report features in Access. Tableau and Qlikview are two popular options. While these can in principle work with an Access database it probably doesn't make sense to do that. To get the best support for reporting and...

Clearly it wouldn't. The substance of the detail line (meaningful attributes) ought to be the key.

That seems like a contradiction to me. Wouldn't invoice number and line item number be the primary key in that case?

The simple answer is data integrity. Let me turn the question around: Why would you want an order detail table that allows duplicate data? Duplicate data means redundancy, ambiguity and potentially incorrect results from your queries. These are some of the reasons why sensible database designs...

If your required recovery point is 15 minutes then use SQL Server or another DBMS with built-in, transactional backups. It's a whole lot easier than trying to achieve the same level of robustness with a Jet/ACE database.

If State determines StateRate then you ought to have two tables instead of one. One table for the state rates; one table for the county rates. The principle that applies here is called Boyce Codd Normal Form, which requires that every determinant should be a key in a table.

The OP explained in his initial post that a uniqueness constraint (whether primary key or unique index) doesn't help because he wants to allow more than one parent of type "Other". That's not true. Having two (non-nullable) parent attributes is not a violation of any Normal Form. I don't think...

The question actually seemed pretty clear to me but I am the only person who has answered it:

Roystreet, Relationships in Access have nothing to do with relational databases. They are actually quite contrary to the spirit and intention of relational databases. The one truly useful feature of relationships is to enforce referential integrity constraints (AKA foreign keys). A foreign key...

You can enforce this kind of rule using a CHECK constraint rather than a uniqueness constraint. For example: ALTER TABLE YourTable ADD CONSTRAINT primary_secondary_parents_must_be_unique CHECK (NOT EXISTS (SELECT 1 FROM YourTable WHERE Type <> 'Other' GROUP BY ChildId, Type HAVING COUNT(*)>1))...

I think Doc_Man and others have answered well enough for my satisfaction thank you: Access is not a database. Fortunately I do need to know what a database is in order to do my job. But as we've established before Rain, you don't speak the same language as many other people on here. I begin to...

It's perfectly OK to have more than one relationship between the same pair of tables. You don't need to do anything "special" to achieve this. Here's an example of my own where the Users table is referenced twice: CREATE TABLE Marriage (Husband INTEGER NOT NULL REFERENCES Users (PersonId), Wife...