Recommended schema websites?

[GopherUK]

Hi,

Just wondering if there is a website out there that gives professional DB schemas? I am sure I saw one here a while ago but I cant seem to find the thread for it.

Any help would be greatly appreciated.

 

[GopherUK]

Thanks guys.

Yeah, I am using a junction table for that reason - to keep address histories. But I am also looking to use junction tables to keep histories of other things, such as product price. I would assume with that sort of set up for product history, it would negate the need for derived data. My professional background is as a business/data analyst and data quality manager but my work was mostly with front-end functionality design/data validation and MI reporting, not in actual database design itself. I've done a lot of IT in the past though, as mentioned above I work with Excel a lot and I have studied technical web design, PHP/SQL/XML etc pretty extensively. I have just never got around to database modelling and I am trying to get as a technical an understanding as possible.

 

[GopherUK]

Hi,

Hoping someone can tell me if I have this schema right for creating a DB that has multiple customers, each with (possible) multiple contacts and I want to be able to keep track of addresses, both active and inactive.

The 'AddressType' field in addresses table is to allow for postal, billing, delivery etc address status. I am actually not too sure how to work this as 1 address could be more than 1 of these defaults. Would I need to create a junction table for this or do I have the option of just having the options (delivery, billing etc) in each address record? And I assume I would use VBA to validate the status update attempt for this by using an UPDATE query to change the status of any other address set to the default billing etc address and update the appropriate record accordingly?

Anyway, I hope I am not talking nonsense and an experienced developer can let me know what they think.

Thanks in advance.

 

[GopherUK]

I am reading Microsoft Access 2007 Inside Out. When it first mentions macros, it says: -

"Macros are a great way to learn about the basics of responding to events and automating actions in an Access database. However, for any application that you intend to distribute to others, you should use Visual Basic to handle events and automate actions. Nearly all the sample databases use Visual Basic exclusively."

Can I ask what the reason for this is?

 

[Kyle123]

Hi

I'll stick my two penny worth in too if you don't mind

Your design looks good, just watch the naming of the fields, it's not good practice to have field names with spaces in.

I'd be awfully tempted to have a way of specifying a default address for a customer. As it stands to get the invoice/delivery address for a customer you would need to also know the correct contact, when you have multiple contacts for one customer in multiple locations how will you establish this?

Address types is an interesting one and could be taken either way, extra fields in the address table for each type or a separate table. Theoretically having them in the same table would be faster as there's one less join, but you'd need to add an extra field to the address table if you needed to add any additional address types. I wouldn't have them as you have set them up though since you can only have one address type per address.

As you seem to be taking normalisation right down to the nth degree or I'm assuming you are if there are tables missing and all the fieldnames suffixed with ID are Foreign keys, you should theoretically have a table of regions also for the address table. Whether you want to bother doing that is up to you, there are pros and cons, it makes reporting by region better, but could be a nightmare for entering new customers - I'd be tempted to handle this client side with address validation (same with the cities) but that's up to you.

I'm not sure if indexes exist in Access, it's been a long time since I've used it, but as many-many join tables tend to get quite big it's worth indexing them both ways to increase performance.

Out of interest how many users would be using this concurrently?

Hope some of this helps,

Cheers

Kyle

 

[GopherUK]

Hi Kyle,

Many thanks for your input.

I will try to respond fully below but you will have to forgive me if I leave anything out as I have been busy all day and I am feeling a little burnt out.

Re: spaces in field names - yeah, I am with you on this. I understand the problem with this and upscaling to SQL and it's only there as I copied and pasted it over. Usually I would thoroughly check everything before I would consider an application finished but I posted it here (without noticing the errors) as I am really only just learning and learning as I go along.

Default address - I think this would be a case of creating the addresses and simply create a field to establish is as the default. I am still working on this. I guess I would be looking at the option of a simple default address or a default contact for a certain type of address i.e. Joe Smith in sales would be the default billing address as well.

The reason I am taking normalisation to the nth degree is that I was/am a data analyst. I kinda cut my teeth in that field working for a major UK bank and their data handling was horrible. I see the benefit of the simplicity of having a single price field for a certain product but that leaves no real pricing history (as you could only attain a price history from derevied data from sales and that is clearly very inaccurate and horrible data management). This, for the record, was nothing like the data problems I faced in my professional experience but I just try to avoid data quality issues... I definitely subscribe to the "better no data than bad data" axiom.

I totally agree with you on the regions thing. I think I would be looking to validate that via an event on change on the country dropdown box on the form in order to populate the regions options with only those for that country via a select query.

How many users - just me for now. I am sorta aiming towards using this a proper database for my own stuff but I am in general just practicing and talking out aload on this forum while I do as I need to get the right advice from the pros out there.

Thanks again!

 

[GopherUK]

Can I ask if there is really any need for data validation on a table when producing a professional application? From a techincal point of view, the end user should typical never be entering data directly into tables and should instead be doing so via forms and forms only. If this is the case, is it not a bit cleaner and better practice to forgo all table-based data validation and instead validate in the form(s) via VBA? Simply put, when using forms there is no need to use table validation and you are better off using VBA in forms?

Also, am I right in thinking that subforms can cause a problem here as they display the data table? In this scenario, the user would automatically be circumventing all data validation except for the table-based validation. Is that correct? Obviously, you can forgo the subform and have the parent form open another form for entry and validation. But is there any work around where you can use a subform and still validate? Can the subform be a true form, complete with it's own validation?

Sorry if I am asking dumb questions but there is alot to learn here!

 

[Kyle123]

Can I ask if there is really any need for data validation on a table when producing a professional application?

This depends on what you mean by validation, this is where people have differing opinions. Some people subscribe to the school of thought that says there should be very little logic in the database and business logic should be handled by the front end/client application. Others that the database should be as robust as possible with very few actual table interactions and as much as possible done with sprocs.

The reality is that it you'll always end up in the middle, to give you a (bit of a simplistic) example from your database.

Default address - I think this would be a case of creating the addresses and simply create a field to establish is as the default

How do you validate that only one address is flagged as default? This could be done in either the database or the client application. Ultimately the more restrictions you put in the database will give you less flexibility in the business logic, but less restrictions generally mean you have to check more/write more code in the client application. In truth none of this really matters if there's only ever going to be you using the database and only really becomes important as more people start to interface with it.

All the databases I have ever worked with are far from 'model' databases since they have to have a balance of practical usability whilst still maintaining their integrity so you'll always have a trade off between the two, I was reading recently about the problems of using AutoIDs as primary keys, but they are still widely used. It's also impossible to plan for every eventuality so you get all sorts of bits and pieces bolted all over the place in the real world as you've mentioned. I used to work for a large UK telecoms company and it was the same there.

With regards to your access specific forms questions, I'll have to duck out of that one since I've no idea - I've never really used access in any great depth. I have used the underlying Jet database and written a front end for it in Excel though - several times oddly enough

The reason I was asking about users is that access doesn't really scale very well thats all.

Hope this helps anyway

Cheers

Kyle